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

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?

Upvotes: 0

Answers (7)

Damir Sudarevic

Reputation: 22187

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.

Upvotes: 7

JeffO

Reputation: 8043

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.).

Upvotes: 0

SimonGates

Reputation: 6111

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

Upvotes: 0

ovais.tariq

Reputation: 2625

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.

Upvotes: 0

user359040

Reputation:

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?

Upvotes: 1