SQL demographics standards?

Question

Is there some standard layout for demographics tables? I'm not too lazy to come up with one, but collectively there has to be a lot of research done on how long my address field should be? City? Do I use 20 or 30 characters for first name? Do I keep phone number in a person table? Address table? Phone number table? Someone has had to come up with a best practice or "standard" when it comes to this stuff. It's just too common...

Answer 1

Off the top of my head I'd advice you to store these in demographics tables:



> First Name

> Middle Initial/Name

> Last Name

> Birth Date

> Gender

> Occupation

> Yearly income

> Marital Status

> Home Owner

> Education

> Number of children



Name fields should probably be 20 characters long because you'd want to avoid SQL injection attacks by shrinking your fields and I can't imagine a name being longer than 20 characters.



Phone number and address line 1 and 2 should be stored in the same table as the one being discussed above. However, its a good practice to store other attributes such as city, state and country (maybe town too) in a geographic table and use geographic key to form the relation. Reason being that if you later decide to use your data for business intelligence, you'd find it a piece of cake to form hierarchies.



If you're using SQL Server, you'd even have the option create various comparison bands (or groups) based on age, income etc. using the Business Intelligence Development Studio.



Hope that helps.

Answer 2

It is a waste of time and space to break a simple table up in to multiple tables (tblName, tblAddress). A name is usually only associated to one address, one email, one cell phone etc. Their is no need to complex things and make multiple tables. You'll end up shooting yourself in the foot when it comes to simple tasks if you do.



I'd buy an SQL book (or read an online tutorial) and familiarize yourself with how a DBMS works, how the engines work, the way they store their information, and how a schema should be setup.



I go by standards of my own--live and learn. What you think is probably going to be fine, 40 chars for a name is plenty, 100 for an address, 10 for a phone number. This can always be changed later. Unless your hurtin' for DB space, feel free to spend it, space is cheap nowadays.

Answer 3

can u help me to differentiate VARCHAR n others..when to use VARCHAR