member database tbl design

[TechBoyJK]

Hi,

I'm working on a project that is going to require a member database, along with keeping some information relative to each member. It is going to be for a community, trading site, where the user's will be able to communicate, have a profile page, etc.

I've tried to normalize the tables as best as possible, taking into consideration performance, and what data is usually grouped together in queries. For instance, if I see where a table could be broken into three parts, but I will never query the data individually, it will always come as a whole, I typically left the table together, rather than break it apart and have to do joins everytime. Not only that, it creates extra columns because each smaller table would require its own id and a column for the parent id.

I've create generic table designs, where I simply name the column and its datatype. I've already built alot of code around this table structure, but before I move any further I want to take another look at what I'm doing. It will be easy for me to mod my code at this point. If you have any advice or ideas on how you might approach collecting the data below, please by all means speak up! I'll be very appreciative. Ask questions, challenge my logic.

I have much functionality working around this structure, but before I launch the site, I want to make sure its as good as can be. Like I said, i made all the functions work, so if I just need to rearrange some things, make some changes, it won't be a big deal. Please advise.


tbl_member_core

member_id (int-identity-pk)
user_name (varchar-30)
type (varchar-16)
status (varchar-16)
status_memo (varchar-128-null)
password (varchar-24)
password_hint (varchar-24)
created (datetime)
created_ip (varchar-15)
last_modified (datetime-null)
last_modified_ip (varchar-15-null)
cancel_datetime (datetime-null)
cancel_ip (varchar-15-null)



tbl_member_contact

contact_id (int-identity-pk)
member_id (int-fk)
organization (varchar-50-null)
first_name (varchar-50)
last_name (varchar-50)
middle_initial (varchar-1-null)
street (varchar-50-null)
apartment (varchar-30-null)
city (nvarchar-50-null)
state (varchar-50-null)
country (varchar-50)
zip (varchar-16-null)
phone_number_a (varchar-20-null)
phone_number_a_type (varchar-16-null)
phone_number_b (varchar-20-null)
phone_number_b_type (varchar-16-null)
fax_number (varchar-20-null)
email_a (varchar-50)
email_b (varchar-50-null)
contact_last_modified (datetime-null)
contact_last_modified_ip (varchar-15-null)



tbl_member_profiles

profile_id (int_identity-pk)
member_id (int-fk)
profile_headline (nvarchar-45-null)
profile_shortline_a (nvarchar-25-null)
profile_shortline_b (nvarchar-25-null)
profile_shortline_c (nvarchar-25-null)
sex (varchar-12-null)
birthday (smalldatetime-null)
marital_status (varchar-12-null)
religion (varchar-25)
ethnicity (varchar-25)
here_for_trading (bit-null)
here_for_dating (bit-null)
here_for_friends (bit-null)
here_for_networking (bit-null)
here_for_serious_relationships (bit-null)
children (bit-null)
smoke (bit-null)
drink (bit-null)
sexual_orientation (bit-null)
sign (bit-null)
home_town (nvarchar-50-null)
home_state (nvarchar-50-null)
home_country (varchar-50-null)
height_feet (smallint-null)
height_inches (smallint-null)
body_type (varchar-25-null)
computer (varchar-25-null)
education (varchar-25-null)
occupation (varchar-25-null)
income (varchar-25-null)
favoriteweb_a (nvarchar-255-null)
favoriteweb_b (nvarchar-255-null)
favoriteweb_c (nvarchar-255-null)
phone_number_a_show (bit)
phone_number_b_show (bit)
location_show (bit-null)
homepage_db_list (bit)
ad_show (bit)
homepage_show (bit)
homepage_last_modified (datetime-null)
homepage_last_modified_ip (varchar-15-null)
last_login (datetime)


tbl_member_events

event_id (int-identity-pk)
member_id (int-fk)
event_time (datetime)
event_ip (varchar-16)
event_class (varchar-25)
event_type (varchar-50)
event_title (varchar-50)
event_description (varchar-500)

 

[Crusty]

Can I ask why you prefix all your tables with tbl_? Isn't it pretty obvious that they are database tables? It seems rather redundant, like saying 'int int_NumberOfEntries = 5;'

 

[KLin]

http://blogs.msdn.com/steven_b...ject-Naming-Rules.aspx

I say get rid of the underscores. You'll probably want to modify the sexual orientation field to be a varchar since someone could be straight, gay, or bi.

 

[nakedfrog]

Originally posted by: KLin
http://blogs.msdn.com/steven_b...ject-Naming-Rules.aspx

I say get rid of the underscores. You'll probably want to modify the sexual orientation field to be a varchar since someone could be straight, gay, or bi.

Some of us like underscores!

Smoking and drinking might merit an upgrade, so it's not an all-or-nothing thing.

 

[TechBoyJK]

Originally posted by: Crusty
Can I ask why you prefix all your tables with tbl_? Isn't it pretty obvious that they are database tables? It seems rather redundant, like saying 'int int_NumberOfEntries = 5;'

For whatever reason, I find it much easier to look at with the tbl_ prefix. I once removed them all, and started twitching.

 

[TechBoyJK]

Originally posted by: KLin
http://blogs.msdn.com/steven_b...ject-Naming-Rules.aspx

I say get rid of the underscores. You'll probably want to modify the sexual orientation field to be a varchar since someone could be straight, gay, or bi.

Good point on the sexual orientation. I guess i'm pretty traditional and it didn't cross my mind that it's an option. I like underscores... I think it makes for easier readability.

 

[TechBoyJK]

cool, the responses are making me more comfortable with my overall design. the gripes are much more about smaller details than over design flaws, which is what I was trying to dig up.

 

[Crusty]

Originally posted by: TechBoyJK
cool, the responses are making me more comfortable with my overall design. the gripes are much more about smaller details than over design flaws, which is what I was trying to dig up.

To be honest I didn't look at the table designs because I can't stand it when I see tbl_ prefixing a table

 

[imported_Dhaval00]

I see some minor issues:

home_town (nvarchar-50-null)
home_state (nvarchar-50-null)
home_country (varchar-50-null)

Are you worried about globalization? If not, then make everything varchar - nvarchar is Unicode-16, so takes more storage. Also, just be consistent throughout the tables. I know some times it can be hard, but it is easier from a maintainability standpoint that everything is either varchar or nvarchar.


homepage_last_modified_ip (varchar-15-null)

I am assuming this is the IP address - is this future-proof? What about IP6 addresses... I know they are longer.


As KLin mentioned, get rid of the underscores. You could use Camel Case instead. Email columns are 50 - that's possible, but I have seen weird things in data warehousing. Phone number type columns could be TINYINT (which takes only 1 byte of storage) - this is iffy based on how people are going to interpret results etc., but this can be thought of as being another normalized table where the PK (of type TINYINT) has the literals of the type of phone numbers. I guess you can use this same logic for other 'types,' too, but again, depends on how you want to normalize.


Finally, your primary key columns should be bigint (unless you don't foresee your data to grow exponentially over time). Beyond that, there are other minor issues I might have missed.

 

[zebano]

home_state (nvarchar-50-null)

I wouldn't use this, I would populate another table and reference that (although this is harder to populate with multiple countries) since it avoids problems querying when there are entries like: Mass, Massachusetts, MA. Of course you can do this on the front end if you wish and leave that an nvarchar (I prefer the unicode option since storage is cheap and you may want to globalize someday). I would go so far as to say that if this is international at all, or you have exchange students, immigrants using the site making first_name and last_name nvarchars might be wise.

ISn't a fax_number just a phone number with type of 'fax'? Since it's may not be guaranteed that they will have a fax I would just make a separate table for phone numbers since it's more extensible than having to add phone_number_x to your table as needed (future proofing).


sex (varchar-12-null) accepts a value of 'yes, please'. Well done sir, well done.

 

[JavaMomma]

I personally avoid allowing nulls in a fields unless there is a good reason. For example...
status_memo (varchar-128-null)
why not make it 'not null' and just put '' ? I get frustrated when I see a database that has a varchar and it is littered with '' and nulls then I end up having to write a query that says ,
where status_memo = '' or status_memo is null ...

"DO Avoid NULLable columns
When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check"
http://www.codeproject.com/KB/database/sqldodont.aspx

 

[troytime]

not a fan of the prefixes. seems pointless and makes it harder to find a table from a list of tables.

 

[Markbnj]

Originally posted by: JavaMomma
I personally avoid allowing nulls in a fields unless there is a good reason. For example...
status_memo (varchar-128-null)
why not make it 'not null' and just put '' ? I get frustrated when I see a database that has a varchar and it is littered with '' and nulls then I end up having to write a query that says ,
where status_memo = '' or status_memo is null ...

"DO Avoid NULLable columns
When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check"
http://www.codeproject.com/KB/database/sqldodont.aspx

You have to check anyway, don't you? You can't just read out a '?' and dump it into the display or whatever else you're doing.

In my view, if a column doesn't have to contain valid data then the alternative should be null.

 

[Crusty]

Originally posted by: Markbnj

Originally posted by: JavaMomma
I personally avoid allowing nulls in a fields unless there is a good reason. For example...
status_memo (varchar-128-null)
why not make it 'not null' and just put '' ? I get frustrated when I see a database that has a varchar and it is littered with '' and nulls then I end up having to write a query that says ,
where status_memo = '' or status_memo is null ...

"DO Avoid NULLable columns
When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check"
http://www.codeproject.com/KB/database/sqldodont.aspx

You have to check anyway, don't you? You can't just read out a '?' and dump it into the display or whatever else you're doing.

In my view, if a column doesn't have to contain valid data then the alternative should be null.

I agree, there's a difference between a NULL string and an empty string. To me NULL means it's unknown, and an empty string means that it's known to not exist.

 

[ivan2]

What is this "sign" column in member profile, like a zodiac sign?

 

[TechBoyJK]

Originally posted by: Crusty

Originally posted by: Markbnj

Originally posted by: JavaMomma
I personally avoid allowing nulls in a fields unless there is a good reason. For example...
status_memo (varchar-128-null)
why not make it 'not null' and just put '' ? I get frustrated when I see a database that has a varchar and it is littered with '' and nulls then I end up having to write a query that says ,
where status_memo = '' or status_memo is null ...

"DO Avoid NULLable columns
When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check"
http://www.codeproject.com/KB/database/sqldodont.aspx

You have to check anyway, don't you? You can't just read out a '?' and dump it into the display or whatever else you're doing.

In my view, if a column doesn't have to contain valid data then the alternative should be null.

I agree, there's a difference between a NULL string and an empty string. To me NULL means it's unknown, and an empty string means that it's known to not exist.

Ok, so you are suggesting that rather than just leaving a table column empty, aka never even making any kind of entry whatsoever, I could populate it with an empty string aka ''

I could actually program the db to use a default value of an empty string vs null. Would that be better? As someone pointed out, I think if I avoided null's alltogether, I could remove "null" searching from any and all queries.

 

[JavaMomma]

empty string not '?' dummy data is a hack.... that is what null is for (reminds me seeing Date.Min to represent a null Date).

How is the UI going to look if you leave the address field blank?
string Address = "";
or
string Address = null?
Chances are when you read that txtAddress.Text it is going to return "" anyways.

Basically what I am saying is - if empty string and null have different meanings then by all means make it nullable if they have the same meaning, then don't allow nulls and simply put that empty string in.

 

[JavaMomma]

Originally posted by: TechBoyJK

Originally posted by: Crusty

Originally posted by: Markbnj

Originally posted by: JavaMomma
I personally avoid allowing nulls in a fields unless there is a good reason. For example...
status_memo (varchar-128-null)
why not make it 'not null' and just put '' ? I get frustrated when I see a database that has a varchar and it is littered with '' and nulls then I end up having to write a query that says ,
where status_memo = '' or status_memo is null ...

"DO Avoid NULLable columns
When possible. They consume an extra byte on each NULLable column in each row and have more overhead associated when querying data. The DAL will be harder to code, too, because everytime you access this column you'll need to check"
http://www.codeproject.com/KB/database/sqldodont.aspx

You have to check anyway, don't you? You can't just read out a '?' and dump it into the display or whatever else you're doing.

In my view, if a column doesn't have to contain valid data then the alternative should be null.

I agree, there's a difference between a NULL string and an empty string. To me NULL means it's unknown, and an empty string means that it's known to not exist.

Ok, so you are suggesting that rather than just leaving a table column empty, aka never even making any kind of entry whatsoever, I could populate it with an empty string aka ''

I could actually program the db to use a default value of an empty string vs null. Would that be better? As someone pointed out, I think if I avoided null's alltogether, I could remove "null" searching from any and all queries.

Ya, that would be my suggestion, you can use a default of '' on the database or have your stored proc or business layer set it as the default or even both.

 

[imported_Dhaval00]

From a purist's standpoint, I'd like to reiterate Mark's and Crusty's view: if something is unknown, it must be NULL. The classic example is a middle name - if you don't have a middle name, it is blank, but if it is unknown, it should be NULL. This is a common scenario in criminal systems where the officers interrogate suspects. Also, according to the SQL ISO standard: Null is defined by the ISO SQL standard as different from both an empty string or the numerical value 0.

 

[JavaMomma]

Originally posted by: Dhaval00
From a purist's standpoint, I'd like to reiterate Mark's and Crusty's view: if something is unknown, it must be NULL. The classic example is a middle name - if you don't have a middle name, it is blank, but if it is unknown, it should be NULL. This is a common scenario in criminal systems where the officers interrogate suspects. Also, according to the SQL ISO standard: Null is defined by the ISO SQL standard as different from both an empty string or the numerical value 0.

I agree with this point, in your example
You could have no middle name (some people don't have one) or it could be unknown, thus null truly does make sense in this case.

However, I'm guessing that the UI would need to look something like this:
Middle Name: [ TEXT Field ] [Unknown CheckBox]
?
However it works - I don't do UI programming, the user is going to have to have a way to specify NULL ...

 

[TechBoyJK]

Originally posted by: JavaMomma

Originally posted by: Dhaval00
From a purist's standpoint, I'd like to reiterate Mark's and Crusty's view: if something is unknown, it must be NULL. The classic example is a middle name - if you don't have a middle name, it is blank, but if it is unknown, it should be NULL. This is a common scenario in criminal systems where the officers interrogate suspects. Also, according to the SQL ISO standard: Null is defined by the ISO SQL standard as different from both an empty string or the numerical value 0.

I agree with this point, in your example
You could have no middle name (some people don't have one) or it could be unknown, thus null truly does make sense in this case.

However, I'm guessing that the UI would need to look something like this:
Middle Name: [ TEXT Field ] [Unknown CheckBox]
?
However it works - I don't do UI programming, the user is going to have to have a way to specify NULL ...

The way I have it setup is that something like Middle name is optional. The database doesn't require an entry, so if the user doesn't submit anything for the value, it is left null.

I'm considering modifying instance like that have a default value of a blank string. That way I don't have to modify my code, and nulls are avoided.

 

[Markbnj]

I have a real hard time distinguishing the semantics of null and "". If I'm reading through a dump and see a "" my first thought isn't: that entity has a value for that column, but we don't know what it is.

My first thought is: there's no value for that column. I don't know about the performance aspects of it, but it seems to me that SQL was designed to use null and not null as the means of signifying whether data was required or not. Null in a column means "value is optional and this record doesn't have it". In the past I probably cringed a bit at seeing "" in database fields. If there's no data why go to the trouble of storing an empty string? But if the performance issues are real then I suppose I could be convinced not to regard "" as a hack.

I'm not too moved by the need to check for null at the UI layer. The purpose of the UI is to translate the program state into something the user can understand. I do write a fair bit of UI code, and checking for null in a column is the least it has to do. If null is the appropriate runtime representation, then the UI jumping through one small hoop is no big deal.