help with sql schema

[TechBoyJK]

I am designing a database for MS SQL, and before getting into EM and actually making it, I am planning everything via a text file before hand... My schema isn't set for any particular language, just something for me to reference... Below you will find an example of what I'm doing, something that applies to the 50+ tables im designing Anyways, I need to make sure there isn't anything insane in my logic, and for the second table, i believe the member_should be the foreign key correct? pk stands for primary key, fk stands for foreign key.. Its assumed that all columns do not allow nulls, so any column that can be a null is marked with NULL

tbl_member_core

member_id (int-identity-pk)
member_user_name (varchar-30)
member_type (varchar-16)
member_status (varchar-16)
member_status_memo (varchar-128-null)
member_password (varchar-16)
member_password_hint (varchar-16)
member_created (datetime)
member_created_ip (varchar-16)
member_last_modified (datetime-null)
member_last_modified_ip (varchar-16-null)
member_cancel_datetime (datetime-null)
member_redirect_url_status (bit)

tbl_member_contact

member_contact_id (int-identity-pk)
member_id (int-fk)
member_organization (varchar-50-null)
member_first_name (varchar-20)
member_last_name (varchar-50)
member_middle_initial (varchar-1-null)
member_street (varchar-50)
member_apartment (varchar-30-null)
member_city (varchar-50)
member_state (varchar-50-null)
member_country (varchar-50)
member_zip (varchar-16)
member_phone_number_a (varchar-16-null)
member_phone_number_a_type (varchar-16-null)
member_phone_number_b (varchar-16-null)
member_phone_number_b_type (varchar-16-null)
member_fax_number (varchar-16-null)
member_email_a (varchar-50)
member_email_b (varchar-50-null)

 

[WannaFly]

In the first table username should be PK, else you'll be allowing duplicate usernames.
Can one member have more then one contact? If not its pointless to separate them into separate tables.
Email needs to be longer then 50 char
firstname should be 50 char, IMHO


On an alternate note: Is there any reasonsing to using tbl_ as prefixs for tables, I dont understand it. Same goes for member_ in front of all the column names, if its under the member table why do you need the prefix member_? I dont designed DB's this way, and have often wondered why people do. I had a project outsourced and the programmer prefixed EVERY column with col_, it was quite...pointless.

 

[TechBoyJK]

Originally posted by: WannaFly
In the first table username should be PK, else you'll be allowing duplicate usernames.
Can one member have more then one contact? If not its pointless to separate them into separate tables.
Email needs to be longer then 50 char
firstname should be 50 char, IMHO


On an alternate note: Is there any reasonsing to using tbl_ as prefixs for tables, I dont understand it. Same goes for member_ in front of all the column names, if its under the member table why do you need the prefix member_? I dont designed DB's this way, and have often wondered why people do. I had a project outsourced and the programmer prefixed EVERY column with col_, it was quite...pointless.

i do it for readability in the application's code. even though the username needs to be unique, the underlying code looks at the member_id 10-1 the username... also, in other tables, the member_id is used to make the data unique (besides the tables identity int)

 

[Markbnj]

You can use a UNIQUE constraint on the user id column if you would rather not make it the primary key.

 

[TechBoyJK]

Originally posted by: Markbnj
You can use a UNIQUE constraint on the user id column if you would rather not make it the primary key.

well, i'll use the unique constraint on the user_name because there needs to be no duplicate user_names, but I think the user_id(int) needs to be the primary key cause all teh data in all the other databases is identified using the user_id

 

[oog]

Originally posted by: WannaFly
In the first table username should be PK, else you'll be allowing duplicate usernames.
Can one member have more then one contact? If not its pointless to separate them into separate tables.
Email needs to be longer then 50 char
firstname should be 50 char, IMHO


On an alternate note: Is there any reasonsing to using tbl_ as prefixs for tables, I dont understand it. Same goes for member_ in front of all the column names, if its under the member table why do you need the prefix member_? I dont designed DB's this way, and have often wondered why people do. I had a project outsourced and the programmer prefixed EVERY column with col_, it was quite...pointless.

While I think that a unique constraint on username is reasonable, I wouldn't make it the primary key. It's possible that you will want to allow someone to change their username. I do like having the contact information separate from the member information. A join on th e foreign key is very easy for the database. I would even consider if you have other postal addresses in your system and decide if you want to separate out the address information. The member_status column is a varchar. If it can be only one of several values, I would consider making it a foreign key to a lookup table.

I think that you should drop member_ from all of the column names.

 

[WannaFly]

Originally posted by: TechBoyJK

Originally posted by: Markbnj
You can use a UNIQUE constraint on the user id column if you would rather not make it the primary key.

well, i'll use the unique constraint on the user_name because there needs to be no duplicate user_names, but I think the user_id(int) needs to be the primary key cause all teh data in all the other databases is identified using the user_id

OP: it doesnt have to be PK to be used as a FK for other tables, just identity would work.. Are you going to be querying most often by ID or username? Whichever you query most should be your PK, because that will create a clustered index on that coulmn, which, given the type of data a username is, increase the performance greatly.

You will at least need to create a non clustered index on your username column to improve performance when searching on that column.

 

[oog]

Originally posted by: WannaFly

Originally posted by: TechBoyJK

Originally posted by: Markbnj
You can use a UNIQUE constraint on the user id column if you would rather not make it the primary key.

well, i'll use the unique constraint on the user_name because there needs to be no duplicate user_names, but I think the user_id(int) needs to be the primary key cause all teh data in all the other databases is identified using the user_id

OP: it doesnt have to be PK to be used as a FK for other tables, just identity would work.. Are you going to be querying most often by ID or username? Whichever you query most should be your PK, because that will create a clustered index on that coulmn, which, given the type of data a username is, increase the performance greatly.

You will at least need to create a non clustered index on your username column to improve performance when searching on that column.

I agree the frequency of using the column for queries is one reason for choosing the username as a primary key, but if you're ever going to allow it to change, then you don't want to make it a primary key. For this reason, I like artificially generated surrogate keys in general. You can always query the member table based on the username, but use the user_id for the joins to the other tables.

 

[WannaFly]

Originally posted by: WannaFly

Originally posted by: TechBoyJK

Originally posted by: Markbnj
You can use a UNIQUE constraint on the user id column if you would rather not make it the primary key.

well, i'll use the unique constraint on the user_name because there needs to be no duplicate user_names, but I think the user_id(int) needs to be the primary key cause all teh data in all the other databases is identified using the user_id

OP: it doesnt have to be PK to be used as a FK for other tables, just identity would work.. Are you going to be querying most often by ID or username? Whichever you query most should be your PK, because that will create a clustered index on that coulmn, which, given the type of data a username is, increase the performance greatly.

You will at least need to create a non clustered index on your username column to improve performance when searching on that column.

While I think that a unique constraint on username is reasonable, I wouldn't make it the primary key. It's possible that you will want to allow someone to change their username.

Even if its the PK, it can be changed as along as it remains unique.

 

[oog]

Originally posted by: WannaFly
Even if its the PK, it can be changed as along as it remains unique.

Ah. I wasn't aware of that. It may still be a pain to allow it to change because then you have to make sure that the changed username cascades to all tables that use it as a FK correctly.

 

[WannaFly]

Originally posted by: oog

Originally posted by: WannaFly
Even if its the PK, it can be changed as along as it remains unique.

Ah. I wasn't aware of that. It may still be a pain to allow it to change because then you have to make sure that the changed username cascades to all tables that use it as a FK correctly.

Yah, at least MSSQL allows you to change PKs as long as they stay unqiue. Regarding cascading, thats why you use member_id as the FK. As long as its identity. No need to cascade then.

OP: Sorry if we hijacked your thread a little, back on track

 

[TechBoyJK]

Originally posted by: WannaFly

Originally posted by: oog

Originally posted by: WannaFly
Even if its the PK, it can be changed as along as it remains unique.

Ah. I wasn't aware of that. It may still be a pain to allow it to change because then you have to make sure that the changed username cascades to all tables that use it as a FK correctly.

Yah, at least MSSQL allows you to change PKs as long as they stay unqiue. Regarding cascading, thats why you use member_id as the FK. As long as its identity. No need to cascade then.

OP: Sorry if we hijacked your thread a little, back on track

no, this is fine.. keep talking about this stuff. I want to get the logic right before I start building the db... most queries are on the user_id, not the user_name.

 

[oog]

Originally posted by: WannaFly

Originally posted by: oog

Originally posted by: WannaFly
Even if its the PK, it can be changed as along as it remains unique.

Ah. I wasn't aware of that. It may still be a pain to allow it to change because then you have to make sure that the changed username cascades to all tables that use it as a FK correctly.

Yah, at least MSSQL allows you to change PKs as long as they stay unqiue. Regarding cascading, thats why you use member_id as the FK. As long as its identity. No need to cascade then.

OP: Sorry if we hijacked your thread a little, back on track

If you're going to keep both member_id and username in the table, why not make member_id the primary key and just force uniqueness on the username? Now that the OP has said that most queries are on the id, you don't have to worry about query speed on username.

 

[oog]

Originally posted by: TechBoyJK
I am designing a database for MS SQL, and before getting into EM and actually making it, I am planning everything via a text file before hand... My schema isn't set for any particular language, just something for me to reference... Below you will find an example of what I'm doing, something that applies to the 50+ tables im designing Anyways, I need to make sure there isn't anything insane in my logic, and for the second table, i believe the member_should be the foreign key correct? pk stands for primary key, fk stands for foreign key.. Its assumed that all columns do not allow nulls, so any column that can be a null is marked with NULL

tbl_member_core

member_id (int-identity-pk)
member_user_name (varchar-30)
member_type (varchar-16)
member_status (varchar-16)
member_status_memo (varchar-128-null)
member_password (varchar-16)
member_password_hint (varchar-16)
member_created (datetime)
member_created_ip (varchar-16)
member_last_modified (datetime-null)
member_last_modified_ip (varchar-16-null)
member_cancel_datetime (datetime-null)
member_redirect_url_status (bit)

tbl_member_contact

member_contact_id (int-identity-pk)
member_id (int-fk)
member_organization (varchar-50-null)
member_first_name (varchar-20)
member_last_name (varchar-50)
member_middle_initial (varchar-1-null)
member_street (varchar-50)
member_apartment (varchar-30-null)
member_city (varchar-50)
member_state (varchar-50-null)
member_country (varchar-50)
member_zip (varchar-16)
member_phone_number_a (varchar-16-null)
member_phone_number_a_type (varchar-16-null)
member_phone_number_b (varchar-16-null)
member_phone_number_b_type (varchar-16-null)
member_fax_number (varchar-16-null)
member_email_a (varchar-50)
member_email_b (varchar-50-null)

If you're going to track email addresses, you might also consider tracking the birthdate. I heard recently (though I haven't looked anywhere official to verify this) that you cannot legally email someone under 13 without some kind of parent or guardian approval.

 

[TechBoyJK]

Originally posted by: oog

Originally posted by: TechBoyJK
I am designing a database for MS SQL, and before getting into EM and actually making it, I am planning everything via a text file before hand... My schema isn't set for any particular language, just something for me to reference... Below you will find an example of what I'm doing, something that applies to the 50+ tables im designing Anyways, I need to make sure there isn't anything insane in my logic, and for the second table, i believe the member_should be the foreign key correct? pk stands for primary key, fk stands for foreign key.. Its assumed that all columns do not allow nulls, so any column that can be a null is marked with NULL

tbl_member_core

member_id (int-identity-pk)
member_user_name (varchar-30)
member_type (varchar-16)
member_status (varchar-16)
member_status_memo (varchar-128-null)
member_password (varchar-16)
member_password_hint (varchar-16)
member_created (datetime)
member_created_ip (varchar-16)
member_last_modified (datetime-null)
member_last_modified_ip (varchar-16-null)
member_cancel_datetime (datetime-null)
member_redirect_url_status (bit)

tbl_member_contact

member_contact_id (int-identity-pk)
member_id (int-fk)
member_organization (varchar-50-null)
member_first_name (varchar-20)
member_last_name (varchar-50)
member_middle_initial (varchar-1-null)
member_street (varchar-50)
member_apartment (varchar-30-null)
member_city (varchar-50)
member_state (varchar-50-null)
member_country (varchar-50)
member_zip (varchar-16)
member_phone_number_a (varchar-16-null)
member_phone_number_a_type (varchar-16-null)
member_phone_number_b (varchar-16-null)
member_phone_number_b_type (varchar-16-null)
member_fax_number (varchar-16-null)
member_email_a (varchar-50)
member_email_b (varchar-50-null)

If you're going to track email addresses, you might also consider tracking the birthdate. I heard recently (though I haven't looked anywhere official to verify this) that you cannot legally email someone under 13 without some kind of parent or guardian approval.

there is actually another table, that collects more information.... my question is, do i do it as a single column, in date time format, or do I do a seperate, day, month, year column?

 

[oog]

a single column in date format should give you all the information you need.