Same primary key in one-to-one relationships?

[cj1997]

Hi
Really sorry if I have duplicated an earlier question (I have been looking and looking, but may have missed it)! I am VERY new to Access, and am in the process of creating a database for my thesis. I have mapped everything out and built it in Access (but haven't entered any data yet). My question relates to structure. The data is based on artifacts, each piece is recorded with an automatically generated primary key in the main table. There are a number of features (type, decoration etc.) that are documented in related tables (included in/linked to the main table with foreign keys). Aside from these (the features I will query on), each piece has a number of characteristics to be recorded (size, colour etc.). Because of form/table size, I have broken the information into three tables (a main table with ID details, a second with Sizing and Dimensions, and a third with Materials etc.).

My question is: Can the main ID primary key from the main ID table also be used as the primary key in the 2 other basic information tables, with these 2 linked to the main table in one-to-one relationships (so it's an auto-number in the main table and an integer in the other two). This to me would be much easier to navigate than to have one long giant table with all these basic details.

Not to press my luck... but my second (and related question): I have created a main data entry form with a "navigation form". 5 tabs/pages. Tab/Page 1 = main ID. 2 and 3 are the two broken apart tables mentioned above. 4 and 5 are forms based on related tables that have their own subforms included. Both 4 and 5 are based on tables in which the main ID primary key is entered as a foreign key (in one-to-many relationships with the main table). Since this was done with a navigation form (instead of adding each of the 4 as subforms), will it still allow me to enter all relevant details for one piece into each tab/page, and save it all together automatically (whenever I enter a new piece into the main ID form)? Will the primary key be automatically generated throughout, or do I have to enter it manually in each tab/page?

I hope that isn't too confusing... I can try to zip the schematic of the structure if it is. Thank you very much for the help!!

cj

 

[plog]

Can the main ID primary key from the main ID table also be used as the primary key in the 2 other basic information tables, with these 2 linked to the main table in one-to-one relationships

Yes, but you probably shouldn't. 1 -1 relationships are usually unnecessary--that data should probably be in with the main table. Does every record in main have a counterpart in the other 2 tables? Or could some records in main not have data in 1 of the other 2?

For your form question I'd need to see what you were talking about.

 

[cj1997]

Thanks for the reply!

Every record will have values to be entered in both other tables. It was really so that I could avoid having 40-50 fields in the main table.

In terms of structure, would it then be better to keep all fields in one main table, and simply split the fields up on different forms (for ease of entry)?

 

[plog]

First the question that doesn't come with a lecture:

In terms of structure, would it then be better to keep all fields in one main table, and simply split the fields up on different forms (for ease of entry)?

The answer to that is yes. Now...

so that I could avoid having 40-50 fields in the main table

That in and of itself isn't bad, but this isn't my first time on the forum: 40 - 50 fields in one table is a sign that you might have an improperly structured database. Again, it might not be, but I'm batting about 800 when I see that many fields and suspect something.

Can you post a screenshot, or even the database itself so that I can see your structure?

 

[ButtonMoon]

My question is: Can the main ID primary key from the main ID table also be used as the primary key in the 2 other basic information tables, with these 2 linked to the main table in one-to-one relationships (so it's an auto-number in the main table and an integer in the other two). This to me would be much easier to navigate than to have one long giant table with all these basic details.

Such a relationship is actually 1 to 0/1 and not strictly 1 to 1 because the row on the referencing side (where the foreign key sits) is optional. 1-1 is usually best implemented in one table but in practice 1-0/1 is much more common and it's a perfectly sensible thing to implement a 1-0/1 relationship using two tables.

Note that the referencing table doesn't have to reference the primary key of the other table, it could be any suitable key (minimally unique and non-null) in the target table. Sometimes it's an advantage to use some alternative key in such cases, e.g. because you need to populate the foreign key value before a surrogate key is generated or because a business key attribute is needed in both tables to support constraints or other logic.

 

[cj1997]

Ok, so I have included a link below to a screenshot of the relationships of the majority of the tables in my db. The main ID table is called "ID". The other tables that currently house the information I was interested in including with the same primary key are "Treatment," "Material," and "Morphology" (they are adjacent to the ID table). 2 of these were the tables I was discussing above.



I was originally going to have these as separate tables, with an auto-generated primary key and the main ID# as a foreign key. But these three tables would all be linked to the main table in one-to-one relationships, so I wasn't sure which structure was better.
thanks again
picture is on flickr dot com slash 'photos' slash 100634461@N08/9554314193

 

[plog]

Wow. I don't think I see anything that screams poor structure. I would eliminate all the 1-1 relationships in your tables and put them into 1 table with 40 - 50 columns.

The only thing that gives me pause is all those tables that link to Forum/Astrom#. Records start off in ID then link to various other tables that have Forum/Astrom# values and then they meet back up at Forum/Astrom#, seems like that could be redundant data that would be better put directly into the ID table.

 

[cj1997]

Thanks for the advice! I will put it all in one main table, and simply split up the form for entry.

I have been struggling a bit with the Furum/Astr issue. I was going to do a related table, with all of the descriptors given a autogenerated primary key (which would then be put in as the foreign keys in the other tables). More laborious than just entering the Furum/Ast ID itself, but those IDs are a combination of letters and numbers (eg. FS201). I was worried that if I entered those directly than querying might be more difficult later? But I am starting to think that it might just be easier to enter them as they are, and not add the additional table; or like you said, risk entering redundant data.