Let's say you have 2 tables with a 1 to many relationship between:

table: main_table;
primary key: main_id;
autokey: main_id;
current values for primary key:1,2,3,4,5


Do I want to do OPTION_1 OR OPTION_2 for the sub_table or does it matter ?


---------OPTION_1----------
table: sub_table;
primary key: main_id & sub_id;
autokey: none;
current values for primary key:1,1; 1,2; 1,3; 2,1;2,2;2,3;2,4; 3,1;3,2;3,3



---------OPTION_2----------
table: sub_table;
primary key: sub_id;
autokey: sub_id;
current values for primary key:1,2,3,4,5,6,7,8,9,10


Am I breaking any rules of normalization if I do Option 1?

Hi
I read your post with interest but am not sure about the question - my understanding of normalization is to store the data only once so there is no redundancy although sometimes it's better to duplicate things to improve speed by cutting down on complex queries.

With the one to many example, let's say you have table people each of whom can own more than one car, if you put all the records into the people table you would be duplicating all the person data, like name, address, tel no etc. That would break the normalization rules and make it hard to update too. Let's say you have a numeric primary key in that table, person_id and it's auto generated so it goes 1,2,3 etc.

Now you can have another table for the cars, with it's own primary key, say car_id that can also be auto generated and so goes 1,2,3 etc.

Now to link the two together you can use a 'foreign key' - that's where you put the key from one table as data in another table. So in this example, let's say I am person_id=3, and I have two cars, in the car table I could have something like . . .

car_id=1 owner_id=3

owner_id is the foreign key, if you want to know who owns this car you use 3 as the key to access the table owners, if you want to know what cars I own, you query table car to find records where owner_id=3

in this example you might take things further by having separate tables for the colour of the car, manufacturer, fuel, body type and anything else that gets duplicated across vehicles.

Sorry if I'm telling you stuff you already know <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>, hope that helps