Good Database Design

[SteveR77]

One thing that is a must to understand in relational database design is the concept of normalization.

Normalization allows data to be broken down into logical parts that can then be related to each other.

The following link provides some excellent examples of normalization as well as defining what it is so it can be understood.

http://www.bus.iastate.edu/mennecke/533/s03/normalization_examples.htm

Cheers,

Steve

 

[svanels]

Another thing that must be understand in database design is what kind of data is stored in the tables. Plain common horse sense is a must. If some people organize their kitchen like their databases, I expect to find shoes in the freezer..

Steven van Els
SAvanEls@cq-link.sr

 

[skiflyer]

Don't forget the times common sense needs to be chucked... zip codes are not best stored as a number... who'da'thunkit.

Normalization is key, if you only learn one thing before setting up a database, learn normalization. Then learn that just cause they're easy to get up and going, doesn't mean you should get it up and going... there's alot there, and double time spent up front almost always means quadruple time saved down the road in database work.

-Rob

 

[SteveR77]

Rob,

The common sense part of setting the data types is if you do not need to do a calculation on it, it should not be defined as a number.

But, as you said that is where normalization comes into play.

Steve

 

[tshot]

i dbase/foxbase era, it was good idea 2 use character for no-calc fields as it saved space. Now with MySql kind of Databases, where we have so may options for INT it self. so we can store zip/pin no as int.

Another issue is indexing if that filed requires indexing. but i have not come across any limitaion of that sort. the advice is to keep the key as small as possible.




----------------
ur feedback is a very welcome desire

 

[r937]

no, storing zip as integer is flawed

tshot, how would you store the US zip code which has the format 99999 or 99999-9999

and what if you want to store both zip codes and canadian postal codes which have the format A9A 9A9



rudy

http://r937.com/

SQL Consulting

 

[skiflyer]

The second half of the question is what do you gain by storing it as an int? It's not just about space, but about manipulation and extensibility.

r937's examples are two good ones... additionally, what kind of searches do you want to run on zip codes, what kind of manipulation might you want to do etc. ZIP codes become a bad example because nobody cares about searching for partials. Although if I'm not mistaken we have some 01025 type zip codes (and if we don't, is there anything in the specification that says we won't?), and while storing leading zeros is generally silly, this is a place I'd recommend it.

-Rob

 

[tshot]

agreed for zip code. but pin nos in india are all numbers (6 digits). so what if we store it as int. and for manipulating or no manipulating, it best 2 keep table stru as small as possible. pls comment




----------------
ur feedback is a very welcome desire

 

[Golom]

My general rule is

If you ain't gonna do math on it then it ain't a number.

The whole point about RDBMS theory is that you shouldn't have to worry about how the data is stored ... and we're long past the time when saving a byte per record matters to anyone (i.e. buy another 80GB drive for 100 bucks if space is a problem.)

ZIP codes, UPCA (i.e. bar) codes, social security numbers, Employee IDs, etc. are all examples of things that get stored as numerics but are never involved in numeric computations and should not be stored as numbers. Think of these things as a license plate ... its only purpose is to provide identification in some domain. Beyond that there is no meaning to be attached to ZIPCode1 + ZIPCode2 = ???

 

[r937]

funny, i used sum(zipcode)/count(*) as my example in a similar thread on another forum recently

"buy another 80GB drive for 100 bucks if space is a problem" -- quote of the month

rudy

http://r937.com/

SQL Consulting

 

[SteveR77]

I don't use zip codes. I use postal codes and they are always text.

The idea as already mentioned is to have an application that is scalable and the US zip and zip plus 4 aren't.

"Think of everything that can change, then plan on what you thought of being only half of what will change."

The quote is my own from when I was attempting to explain DB design and project life cycles to someone.

 

[Golom]

Rudy

I didn't see that thread. I know that solutions posted here including (especially?) mine, are somewhat artificial, but what meaning could sum(zipcode)/count(*) have? Average Location perhaps?

Well ... "quote of the moment" anyway.

 

[r937]

yes, "average zipcode"

i was using that as an example of a ludicrous mathematical operation on a numeric zipcode

the thread was on a forum on, ahem, a completely different site

rudy

http://r937.com/

SQL Consulting