Keep a friend table that holds the UserID and then the UserID of the friend (we will call it FriendID). Both columns would be foreign keys back to the Users table. Somewhat useful example: Table Name: User Columns: UserID PK EmailAddress Password Gender DOB Location TableName: Friends Columns: UserID PK FK FriendID PK FK (This … Read more
However there are some companies of course who fear that their data might be compromised, so we are evaluating other solutions. This is unfortunate, as customers sometimes suffer from a misconception that only physical isolation can offer enough security. There is an interesting MSDN article, titled Multi-Tenant Data Architecture, which you may want to check. … Read more
The SQL DDL (data definition language) statements could look like this: CREATE TABLE product ( product_id serial PRIMARY KEY — implicit primary key constraint , product text NOT NULL , price numeric NOT NULL DEFAULT 0 ); CREATE TABLE bill ( bill_id serial PRIMARY KEY , bill text NOT NULL , billdate date NOT NULL … Read more
Advantages are that you keep the history (good for auditing) and you don’t have to worry about cascading a delete through various other tables in the database that reference the row you are deleting. Disadvantage is that you have to code any reporting/display methods to take the flag into account. As far as if it … Read more
Your source is in no way official. It dates to 2011 and I don’t even recognize the authors. If the money type was officially “discouraged” PostgreSQL would say so in the manual – which it doesn’t. For a more official source, read this thread in pgsql-general (from just this week!), with statements from core developers … Read more
There is already an ISO standard for this; no need to invent your own scheme: http://en.wikipedia.org/wiki/ISO_5218 Per the standard, the column should be called “Sex” and the ‘closest’ data type would be tinyint with a CHECK constraint or lookup table as appropriate.
I found SchemaSpy quite good – you have to run the script every time schema changes but it is not so big deal. As pointed out in the comments there is also a GUI for it. Another nice tool is SchemaCrawler.
What is a unique index? A unique index on a column is an index on that column that also enforces the constraint that you cannot have two equal values in that column in two different rows. Example: CREATE TABLE table1 (foo int, bar int); CREATE UNIQUE INDEX ux_table1_foo ON table1(foo); — Create unique index on … Read more
I think that your model #2 is fine, however you can take a look at the more complex model which stores questions and pre-made answers (offered answers) and allows them to be re-used in different surveys. – One survey can have many questions; one question can be (re)used in many surveys. – One (pre-made) answer … Read more
One item is going to have many tags. And one tag will belong to many items. This implies to me that you’ll quite possibly need an intermediary table to overcome the many-to-many obstacle. Something like: Table: Items Columns: Item_ID, Item_Title, Content Table: Tags Columns: Tag_ID, Tag_Title Table: Items_Tags Columns: Item_ID, Tag_ID It might be that … Read more