Link to home
Start Free TrialLog in
Avatar of matt_m
matt_m

asked on

Database Design Question

Hi All,

I've been charged with building a small Access database at work and was hoping to have some input on something that puzzles me.  

I understand (well I think I do) the general principles of normalisation but was wondering what are the advantages of creating extra tables with only two attributes (a key plus data point) instead of adding the datapoint into the table.  For example imagine one table lists cars (attributes are owner, year made, mileage and colour).  If the cars can have one of say ten colours is it better practise to create a table with these colours and then link that to the car table or include the colours in the car table?  I get that if one wanted to universally change one of the colours (eg blue to electric blue) it would be easier to have a seperate table so it only needs to be changed once but that seems to be a small benefit for the added complexity of the extra table.  

Any general comments welcome and appreciated!
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

I don't think another table for color is a good idea.  It's an attribute of the car and a single piece of data.  If the owner info includes address and other info, then owner is a good candidate for another table because of the amount of data associated with the owner.
Avatar of matt_m
matt_m

ASKER

Thanks for your reply David. Note I'll leave the question open for a day or so and see what different opinions people have to say.
You should get a lot more in the morning...
Personally, I *would* have another table with colours and use it as a record source for say a combobox. This wil ensure all users spell BLUE the same way for later reporting. But that is the only reason and nothing to do with normalisation of data. In fact, I would not even have a relationship between cars & colours!
Normalising is more about maintaining relationships and only storing data that is relevant. Minimise duplication.
As an example, lets say one car may have many owners over a period of time.
I would have an Owners table related to the Cars table, one to many. This would allow me to track all owners of that car.
CAR>>One to Many >> Owner

The opposite scenario would be to track every Car a person has Owned, I would have a similar (but opposite) relationship.
Owner >> one to many >> Cars

If you want to expand on that and track every owner of every Car and every Car owned you can do it with three tables:
Car >> many to one>> Car/Owner >>one to Many >>Owner.

The first two example really describe the purpose in the simplest way.
The example databases that are provided by Microsoft are a good starting point. Look at Customers and Orders in Northwind>

Cheers
JC

SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Let's supose that your app use a combobox for assigning colours to cars.

If you have a table you can fill the combobox with a query. And when a colour changes you don't need to modify the code of the combobox to reflect this change.

What if your database has 1000 colours and instead of using one only combobox you have it in several different forms (say 4/5)? In this environment, any change to colours collection will become a truly pain in the ass.

If you has a field with a defined and limited collection of possible values it's always a pretty candidate to become part of a separated table, however, depending on the expected variations on the life of the data and the size of your app you can decide to treat it as a simple field included into the main entity.

The theory says: use an alternate class.
The real world, however, expects you to think a bit and adjust the theory to your projects scope.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
too add:
- advantage is user will need to manage only 1 table for all the "system data" instead of several "system data" table.

OP
IF u take the easy way of including the colors in the Car Table everything would be great at first and everyone would be happy.But some time later it could be hours,days,months,year u are required to make some changes...Search everywhere ask anyone....u will see that the "extra" table that u thought it was useless and u dump it was the way to GO.U will make modifications,Updates,code changes....some time later u will hit a brick wall....
SO use the extra table....normalization seems like extra work ...but it keeps thing neat and tide,it uses the least space and YES it can make ur life very easy for the Future
Just my 0.2 c
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It needs to be said that moving an attribute into a separate table and replacing it with a foreign key that references that table has absolutely nothing to do with normalization. It may help you for other unrelated reasons but that's a separate issue and should never be confused with the issues that normalization is concerned with.
Hi
My view is that Best Practice is to put attributes that have a defined list of values in separate tables.
If only because this makes it possible to have drop-down lists for users to select from valid values.
Here are two examples of colours for cars from my Database Answers Web Site :-
http://www.databaseanswers.org/data_models/car_parking_enforcement/index.htm
http://www.databaseanswers.org/data_models/car_parts_suppliers/index.htm

Regarding normalisation. my view is that breaking-out attributes into separate tables is certainly in the spirit of normalisation.
I have looked through the writings of Ted Codd (the genius behind normalisaiton) , including his original 1970 Paper, but the situation is not clearly defined :-
http://www.databaseanswers.org/codds_page.htm
 
HTH

Barry Williams
Avatar of matt_m

ASKER

Thanks Everyone - I added points for the comments that had some extra resonance for me (this doesn't detract from the other excellent comments)

Main things I will take from this:
- if the attribute is an object it should definetly have an extra table
- the answer is to a large extent dependant on the nature of the database
- a universal lookup table can be useful

Cheers All!
<<- a universal lookup table can be useful>>

  and just to be very clear; not proper, but useful.

JimD.
Nice summation BTW...

JimD.