Dec 13 2003, 08:59 PM
Do these relationships seem correct?
Dec 14 2003, 12:30 AM
I don't see any "relationship types" here... like one to many, or one to one, or anything...
Dec 14 2003, 12:36 AM
The image is pixelated due to the size of the image ...
If you place your mouse cursor on the image then click the "Expand to Regular Sized" button that will appear in the lower right of the image .. you can view the image clearly.
Dec 14 2003, 06:52 AM
Your relationships generally seem correct. However, there is a problem where you have a double link between stock items and statuses and between lots and units. You are linking stock items to lots and through lots to Statuses and you have a direct link between stock items and statuses. Similarly, you have a link between lots and statuses through to units and a direct link between statuses and units. The direct links should not be there.
I also notice that you seem to have an unusually larger number of many to many relationships and wonder if this truly reflects the nature of your data. Many to many relationships are relatively rare in real life.
You also do not have referential integrity enforced in any of the relationships.
I generally try to have at least three fields in junction tables, a primary key identifying the junction, and a foreign key for each of the related tables. Some of your junction tables appear not to have a primary key at all and others appear to have one of the foreign keys acting as a primary key. In order to ensure that I don't duplicate any particular combination in a junction table, I create a unique index on the combined foreign key fields.
Dec 14 2003, 09:15 AM
I see many relationship tables with the symbols of one-to-many and one-to-one. I don't know why my table doesn't show this. Is there some kind of option I need to turn on?
Dec 14 2003, 09:21 AM
You only see those symbols once you have turned on referential integrity. Right click on the join line and choose "Edit Relationship" then check the Enforce Referential Integrity box.
Dec 14 2003, 09:24 AM
Maybe to help I should explain some of the tables.
StockItem is a generic item that I must keep in stock that can only have a status of needed or ordered.
Lot is a subset of stockitem that can can only have a status of In Stock or Expired.
Unit is a subset of Lot that can only have a status of In Use or Disposed.
I do have alot of many-to-many relationships in this database. For instance a stockitem can have many physicalstates. (gas, liquid, solid) and physicalstates will have many stockitems.
Maybe I need to reorganize my tables with respect to statuses but I'm not sure how.
Dec 14 2003, 09:36 AM
My comment about the number of many to many tables wasn't intended to say that you shouldn't have them but only to suggest you review the relationships to ensure that each of them really is many to many. From what you have said in your latest post, it sounds like you have a pretty good handle on the nature of many to many relationships.
Dec 14 2003, 10:04 AM
Yeah, I was stating the fact really to ensure that you agreed with my perseption of what a many to many relationship was. I'm very new as you can see. Since StockItems, Lots, and Units, can only have specific Statuses, do you think these relationships will work or should I create three differect tables with only the statuses that are availabe to each.
For instance, tblStockItemStatuses, tblLotStatuses, and tblUnitStatuses?
Dec 14 2003, 10:49 AM
If there are a lot of similarities in the status descriptions, it makes more sense to have a single table of statuses which you can then use as a lookup for the particular value applicable to main record. That way you only need to worry about maintaining one status table that will contain all potential status values. You will, of course, need to ensure that only an applicable status is applied in any particular table. If you ensure that data is only accessed through forms, you can use option groups and case structures in the vba behind the group to restrict user choices accordingly.
Strictly speakig it is not necessary to have a permanent reltationship between a detail table and a lookup table. Some would go so far as to say you shouldn't have a relationship in that case. The strict interpratation is that you only need to have a permanent relationship when you need to enforce referential integrity. Where you need values for lookup purposes you can establish temporary relationships in queries when you need them.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here