[thelist] Database - advantage of mulitple tables?

Jon Molesa rjmolesa at consoltec.net
Mon Aug 17 14:30:08 CDT 2009


*On Mon, Aug 17, 2009 at 04:14:28PM +0000 rogerharness at comcast.net <rogerharness at comcast.net> wrote:

> We???re working on a database in my office, that will be used to track training. Currently, we have an employee table with a little over 4000 names, that contains such info as: UserID, UserLName, UserFName, UserClassification, UserRegionDivision, UserEmail, UserCity, UserEmpStatus, UserSupervisorLName, and a few others. Can you help explain why (or why not!), it would be good to break down some of this info into multiple tables? I???m thinking we should at least have a User table, a Classification (position) table, and a RegionDivision table. But the only reasons I can come up with would be in case we needed to change any of these fields, we???d only have to edit the Classification or RegionDivision table and not have to go through the entire employee table. Is this correct? 

That is exactly the reason for splitting data out into multiple tables.
It's called data integrity.  Though you could update 4000 records with a
simple query when a Classification or Region/Division name changes, but
why?  The point of good db design is not having to.  It's a bit more
extra work but by having a list pulled from a Classifications table
presented to the user upon inputting a new record the margin of error is
greatly reduced.  Spelling errors are more easily controlled.  Assign a
unique id to every employee (employee_id), go over every field in the
table and ask yourself "Does this field depend upon the employee_id
field?" if not then it's a candidate for a separate table.  It's a
process called Normalization.

> Would there be other reasons we should try to create multiple tables, or would it be simpler just to have the one large table? With only 4000 rows for this table, would speed be an issue at all, either way? 

Initially it is simpler to set up.  But any convenience gained here will
be paid for down the road when you want to update something.

-- 
Jon Molesa
rjmolesa at consoltec.net
if you're bored or curious
http://rjmolesa.com


More information about the thelist mailing list