One-to-One Relationships

[superlibrarygirl]

Hello all,

I'm creating an inventory database for an antique shop/vintage clothing store that consists of tblInventoryMaster (which houses the InventoryID and information that applies to all inventory, like pricing, date purchased/sold, description, etc.) as well as a number of sub-inventory tables, like tblFurniture, tblDresses, tblPants, etc. that house the specific attributes for each sub-inventory category. (So furniture obviously has different attributes than clothing, and dresses will be concerned with things like sleeve types, while pants wouldn't be.)

Here is the problem I'm running into: The PK in tblInventoryMaster is the InventoryID, which I initially had as a FK in the sub-inventory tables. But then I was thinking that since there should only ever be one sub-inventory record per InventoryID, then there should be a one-to-one relationship between the InventoryID in tblMasterInventory and each inventory sub-table, which means that the InventoryID would need to be the PK in all of those tables. (Microsoft's description of one-to-one relationships says that they are not frequently used but that one instance when you would use them is when you have attributes that apply only to subsets of your data, which sounds like what I am doing.) So I deleted the sub-inventory PKs and made the InventoryID the PK in all of the sub-inventory tables.

Unfortunately, while I was able to create a one-to-one relationship between tblInventoryMaster and the first inventory sub-table, after that I get an error that says "Invalid field definition 'InventoryID' in definition of index or relationship" which makes me wonder whether I can only create one one-to-one relationship off of the tblInventoryMaster's InventoryID field.

Would someone be able to clarify whether it is possible to create multiple one-to-one relationships like I am talking about or whether they should be one-to-many relationships even though they aren't actually one-to-many relationships? Is there something else that would cause this error (it happens regardless of which sub-table I try to use to create the second relationship)?

Is there a different way I should be approaching this? My thinking for having the InventoryMaster table was that it would eliminate the need to repeat fields that apply to all of the inventory while allowing me to use the sub-tables for attributes applicable only to subsets of the data. But now I have run myself in circles and am thoroughly perplexed.

Thank you in advance!

 

[jdraw]

Read and work through this tutorial. I'm sure it will help a lot.

Good luck.

 

[superlibrarygirl]

Read and work through this tutorial. I'm sure it will help a lot.

Good luck.

@jdraw - Thank you for the tutorial; it is quite interesting and I will have to spend some more time with it. After reading through twice though I'm having trouble extrapolating to what I need to do because the example used does not seem to be dealing with one-to-one relationships, which is what I'm trying to understand. Perhaps reconceptualizing my organization as you seem to be suggesting is like staring at a visual puzzle for so long that I can't find the vase between the two faces... Could you be a bit more explicit about what you would suggest in this particular instance?

Thank you!

 

[jdraw]

It is not common to have a number of 1:1 relationships.
Usually tables that are related 1:1, can be collapsed to 1 table. In some cases, people/designers create specific 1:1 relationships to store confidential info in the second table. The relationship and table(with confidential info) do not have to be available for most processes.

The tutorial I mentioned is one that starts with business facts/ideas, then moves to tables, attributes and relationships. I was suggesting you step back at your "business' description" and walk through the approach with your data.

There are other tutorials at
http://www.rogersaccesslibrary.com/forum/topic238.html which may be more appropriate for your specific application.

In addition there are a number of free data models at
http://www.databaseanswers.org/data_models/index_all_models.htm
which may give you some ideas.

Also, if you prefer videos

Video tutorials:
If you google for videos "normalization langer" you will find a series of free videos by Dr.Art Langer. These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

 

[Pat Hartman]

This is actually one of the valid uses of 1-1 relationships. The "master" table which includes a record for each item will have an autonumber PK and a type field which indicates what class of item each record belongs to. The inventory master table includes all common columns. Then the "child" tables will all have the InventoryID as their PK (and also the FK) but it will be defined as long integer. The child tables have all the columns that are unique to that inventory class.

There is no problem having many "child" tables in this schema and in fact, that is what I would expect. Your problem is probably that the child tables have their PK defined as an autonumber rather than a long integer.

You will have to include special code in the main form to prevent people from accidentally changing an item's class. If you do decide to allow it, you need to delete the no longer valid record from the original class table and create a new one in the new class table.

 

[superlibrarygirl]

Pat - you are brilliant! When I changed the PK access automatically turned that field into an autonumber and I never thought to check that because I'd had it set as a long integer like you said. The sub-table that worked was one that I must have caught the change without really paying attention.

Thank you so much! This was like staring at code trying to find the missing div tag...

 

[jdraw]

Thanks Pat. I should have picked up on the Generalization/Specialization. I went through subtype and supertype with someone not that long ago.

superlibrarygirl,

You may find more info on the concept and usage etc if you google

Generalization/Specialization or
Supertype subtype data model or
Enhanced ER Models

Here are a few links
http://technet.microsoft.com/en-us/library/cc505839.aspx
http://stackoverflow.com/questions/7044448/supertype-subtype-database-schema-question
http://www.crcnetbase.com/doi/abs/10.1201/9780203486054.ch8

Good luck with your project.