One-to-one relationship or One-to-many?

Question

Maybe I need more coffee this morning but here goes...

I have a very simple inventory system. Right now I have two tables: Items and Inventory.

Items
Id
Title
YearReleased

Inventory
Id
ItemId(Foreign key to Items)
Quantity
QuantityOnHand

Each item has one inventory and each inventory belongs to one item. The relationship between the two is one-to-one. However, when I diagram this out, the relationship based on my setup thus far is a one-to-many, due to the auto-incrementing id I have for Inventory.

Now, I could make this one-to-one by eliminating the auto incrementing id in the Inventory table, but this makes me feel dirty. I always use internal id's for primary keys.

I see a few options:

1.) Remove the auto incrementing id field in Inventory and live with the dirty feeling.
2.) Keep the tables as-is.
3.) Merge Items and Inventory into one table: ItemsInventory.
4.) Something else?

Answer 1

Since many ORMs require a single auto-increment PK, I would:

4) Add a unique index to Inventory.ItemId and it should show as a one-to-one relationship.

Answer 2

If your relationship is really one to one, drop the Id from the Inventory table and use ItemId as PK and FK. Also, name both keys ItemId -- helps.

Answer 3

Would making ItemId have a constraint to be unique be insufficient? That seems to satisfy your requirements.

Answer 4

If you're certain that the mapping will always be 1:1, then merge the two tables into one.

However, are you certain that the relationship will allways be 1:1?

Answer 5

If what the table structure you have mentioned is going to remain as it is now, then I think you should merge Items and Inventory into one table: ItemsInventory.

For such small tables it doesn't make sense to partition them vertically. That way you would remove an extra join. Select on a single table is always faster then joins.

Answer 6

I would merge the two tables together my main reason is you will have duplicate data as well as unnecessary data if you stick with two tables. Querying will also be faster! Looking at the two tables I would merge into one for sure.

Items Id Title YearReleased

Inventory Id ItemId(Foreign key to Items) Quantity QuantityOnHand

You will have two less collumns full of data if you merge to one table ("ID" ItemID" can be dropped). Writing your logic to retrive and send data to the database will also be easier for you.

I would have this table:

**ItemsInventory**
Id
Title
YearReleased 
Quantity
QuantityOnHand 

However you must be sure it is a one-one otherwise you may have give yourself a lot of work if the bussiness needs change.

Simon

Answer 7

If you really want to make it a simple inventory system, then merge the tables.

Reasons not to merge the tables/it doesn't stay simple.

How many items will NOT have an inventory record? Your example only shows a few inventory fields and maybe that's all it would ever have. But it the fields you track in inventory grow and there is a large part of items not in inventory, you're going to have a lot of null fields.

How often will inventory get updated? If these fields are a result of other transaction tables (purchases and sales) being updated frequently, no reason to constanatly update the items table just because inventory was purchased or sold. In your current system, users are expecting these values to be real time (If not then they know what isn't up to date since they didn't make the changes.).