Table Design Tutorial

[bob3940]

I am new to SQL databases and having some trouble locating information on designing tables.

I am trying to find something that falls between "So this is your first SQL database" and "How to configure your SQL server farm for optimal throughput". All of the material I find appears too be to simplified or too advanced.

I understand the basics of table design and database concepts like normalization. I want to find examples of tables that contain common data (ie. name, phone number, address info, part numbers, etc) with an explanation of what data types are chosen and why.

For example. I know that if I ask a user for a phone number that they may enter it in many different formats. With dashes, parentheses, spaces, etc. I also understand that I want to store that data without all of the "extra" formatting. What is a good practice? should I store it in a varchar field? What are some good examples? If I store the data using the suggested formatting what are some considerations I will need to take into account on the client side of the application?

There is a lot of basic data like this that I'm sure there are some good/better/best ways of storing. Zip codes, state abbreviations, social security numbers, etc. I can create a database that will store all of this data but I would like to learn commonly accepted best practices for storing this information so that I am doing it the "right" way.

I would appreciate any suggestions for websites or books that I can reference.

 

[Sensibilium]

For telephone numbers, zip codes, state abbreviations, social security numbers I would use VARCHAR, as they really are that type of data.

Use INT's for actual numbers (SMALLINT, MEDIUMINT, etc. for numbers with a range). SMALLINT(1) should also be used for boolean values 0=FALSE, 1+ =TRUE. You may also want to consider using INTs for currency too, if you use doubles you can introduce calculation errors (multiply by 100 when storing to db, divide by 100 when displaying).

For TEXTAREA's use TEXT type.

For dates and times, that's up you, some people swear by TIMESTAMP, others prefer DATE, TIME, and DATETIME (I personally am undecided even after many years of usage).

I recommend avoiding using BLOBs unless you absolutely must, they just fill up your database too quickly, and the bigger it is, the more unstable it becomes.

As you can probably tell, there is no "right" way to do this, it's mostly down to your personal preference and needs.

The MySQL documentation is worth checking out:

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Ahdkaw

http://www.sensibilium.com/

 

[r937]

adhkaw, just a small correction

you suggested SMALLINT(1) for boolean values

TINYINT is more appropriate

i suspect that you think that the number in parentheses defines the range of values that the column can hold, but it has nothing to do with that

SMALLINT(1) and SMALLINT(11) and SMALLINT(937) all hold exactly the same range of values, which is -32768 to 32767



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon