Sruggling to design with referential integrity

[Hend]

Hi everyone,

I hope I'm able to explain myself with the problem I'm having. I've provided a couple of screen capture which may help.

I'm using Access 2007 and I'm trying to design a database for my stock inventory which I can then use to interrogate for order, purchasing stock check purposes etc.

My product stock has a few different types of steel extrusions such as Box, Angle, Flat etc. I have tables with fields which describe their differing attributes. Each of these type of steel extrusions will have actual stock associated with them, the most important attribute of which is the length of the item. This particular stockItems table has the same fields for every single type of extrusion and I would like to have just one table for this which keeps track of all of the actual stock I have rather than my current design which as to repeat this table for every type of extrusion.

The first image on the left is the way it's set up now, which has to repeat a lot of the same fields in differing tables for the actual stock items.

The second image is how I want to design it, but the problem I'm having is that I can't enforce referential integrity, which I keep reading is a very important aspect of database design.

Does anyone have any idea how I can design this so I have one table to keep track of track items and mainting referential integrity.

I'm sorry if this isn't explained very well and is a little long!

Kind regards,

Dave

 

[HiTechCoach]

Does anyone have any idea how I can design this so I have one table to keep track of track items and mainting referential integrity.

I have found the if I can not enforce "referential integrity", then I have a design (normalization) issue.


Since this is an inventory database, I would suggest that you read this:
Inventory Control: Quantity on Hand

 

[Hend]

I have found the if I can not enforce "referential integrity", then I have a design (normalization) issue.


Since this is an inventory database, I would suggest that you read this:
Inventory Control: Quantity on Hand

Many thanks fo ryour reply. I'm not sure if the link helps much much unfortunately, it doesn't really give a clue how to solve my problem. The database does work the way I've set it up in the second image (see attached) but I can't enforce referential integrity. I can't really see a way round this apart from replicating the stockItems table for every product type in order to maintain referential integrity. This obviously duplicates the information many times over.

 

[alktrigger]

You're problem is coming from the fact that in Image 1 you have one ItemCode in Angle going to many ItemCode's in AngleStockItems (ASI), which means that Each ItemCode in ASI has a single and unique record in Angle. In Image 2, each ItemCode in Angle/Flat/Box is unique, but when you reference them from the stock table's ItemCode, Access does not recognize which table you are referencing. My solution to this would be a join table (tblProduct for my example) where it has fields pkProductID, fkAngleID, fkFlatID, fkBoxID. Where each item in the Angle table would have a record in the tblProduct with only an entry in fkAngleID. Each item in Flat would have a record in the fkFlatID, etc.

Once you have this done, change the relationships as follows
- StockItems.ItemCode to tblProduct.pkProductID (1 to 1)
- Angle.ItemCode to tblProduct.fkAngleID (1 to Many)
- Flat.ItemCode to tblPrdoduct.fkFlatID (1 to Many)
- Box.ItemCode to tblPruoduct.fkBoxID (1 to Many)

I hope this helps and/or makes sense.

 

[alktrigger]

here is a screenshot of how I would do it. Make sure to change tblStockItems.fkProductID Indexed to "Yes (no duplicates)"

 

[Hend]

here is a screenshot of how I would do it. Make sure to change tblStockItems.fkProductID Indexed to "Yes (no duplicates)"

Wow...many thanks for that! I'll have a go at implementing this and I'll let you know how I get on. I really appreciate you taking the time to help.

Dave