one to many relationship

[mountainclimber]

Hello.



I am setting up relationships in MS Access. In the screen shot below I have the relationship ColName and Factor.


ColName has all of the unique 'names' listed. There are about 1350 of them. The field Factor has a massive list of 800k names with many repeats of the names. However, all of the 800k names can be found in the ColName field in the tbl_NameDescs table.

I have made this relationship for two reasons:
1) To maintain name integrity: so the names can only be changed in one place and all names are found in any table can be found in tbl_NameDescs
2) To save space: I don't have to list the same name over and over again in the Factor field.

My question:
In #2 above, when I created the relationship (dragging and dropping while in relationships) the Factor field still lists all of the names. How is that saving space? Shouldn't the names be replaced with an ID or something in the Factor field? ColName and Factor are Text fields.

[nick404]

Why do you have 4 primary keys in tbl_HistStatsSettings?
If all the names can be found in ColName that are in field 'Factor' then Factor wouldn't be the primary key, especially since you said the field factor has many repeats. I don't think it exactly 'saves space' by making these relationships, unless you have an index made it just makes lookup/reference easier for access to conduct on that field. Otherwise relationships just help to normalize the data.

Typically relationships are 1 --> many, and are Primary Key (1) --> Foreign Key (many).

[mountainclimber]

Why do you have 4 primary keys in tbl_HistStatsSettings?

Google "multi-field primary key": I made one primary key from 4 fields in that table.

Factor is a foreign key to ColName primary key. Factor is also "a part" of the primary key of the tbl_HistStatsSettings table. Stat is a foreign key to the StatName primary key.

Does that change your reply?

[June7]

I avoid compound primary keys. I would likely use an autonumber as PK and set the other 4 fields as compound index to prevent duplicate combinations.

[mountainclimber]

I avoid compound primary keys. I would likely use an autonumber as PK and set the other 4 fields as compound index to prevent duplicate combinations.

Could you explain that a little more? I get the autonumber as pk. I've added that now. I've also added ID_FtrAggSttDays as the compound index. So then when I go to create the relationship I should use the autonumber pk?

Edit: answered my own questions. Answer is yes. Thanks.

[nick404]

Keep the relationships as they are, you will just have autonumber pk in your main table instead of the compound one.
Keep the keys the same in the other tables.
ColName(PK) -> Factor (FK, not a pk)
StatName(PK) -> Stat (FK, not a pk)

[ssanfu]

1) To maintain name integrity: so the names can only be changed in one place and all names are found in any table can be found in tbl_NameDescs

To have this, you must have a PK field that is related to a second table with a foreign (FK) field. The name is only stored in the primary table.

2) To save space: I don't have to list the same name over and over again in the Factor field.

Text type fields usually take up more space. So, again, if you have an autonumber PK field linked to a FK field, this takes less space that linking (relating) two text type fields.


BTW, (it goes without saying - but I'm going to say it ) the linking fields, PK and FK, must be the same type of fields.

It may be over kill, but I have a PK field (autonumber) in every table. Maybe it is not used, but if I ever need it, it is there - no table redesigns.

I never use a compound PK field. It is better (easier) to use a compound index to eliminate duplicate records being entered.

(Edit - Oops, I see that June already commented on the compound PK fields)


Maybe these sites will help:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

[June7]

Doesn't hurt to hear (read) it again.

[mountainclimber]

To have this, you must have a PK field that is related to a second table with a foreign (FK) field. The name is only stored in the primary table.



Text type fields usually take up more space. So, again, if you have an autonumber PK field linked to a FK field, this takes less space that linking (relating) two text type fields.


BTW, (it goes without saying - but I'm going to say it ) the linking fields, PK and FK, must be the same type of fields.

It may be over kill, but I have a PK field (autonumber) in every table. Maybe it is not used, but if I ever need it, it is there - no table redesigns.

I never use a compound PK field. It is better (easier) to use a compound index to eliminate duplicate records being entered.

(Edit - Oops, I see that June already commented on the compound PK fields)


Maybe these sites will help:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

I think I may do the same.... thx!