First Timer: Product Inventory Table

Anonymous
2013-04-03T18:19:00+00:00

Creating a Product Inventory database to keep track of all the promotional items my company has. So far I have three tables: Suppliers, Inventory, and Event. In my Inventory table I have the following columns (in order): Product ID, Product Name, Description, Logo, Color, Size, Total Quantity, Supplier, Event, and Storage. There are so many different styles of shirts that we have accumulated over the years that for every shirt style there are multiple sizes and sometimes multiple colors. So with that, I have choosen to add the size and/or color to the product name as well as in the size and color columns.

For example:

Men's Wicking Polo: Small                             S

Men's Wicking Polo: Medium                        M

Blue Crinkle Cut Paper                                           Blue

Purple Crinkle Cut Paper                                        Purple

Will this create a problem? I didn't know how else to set it up because it didn't think the database would recognize that five Crinkle Cut Papers are different colors (making it five individual products because of the colors), if the product name on all of them is the same.

One more: I tried to run some relationships like between the Suppliers table and the suppliers column under Inventory but it didn't work.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

46 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,840 Reputation points Volunteer Moderator
    2013-04-08T18:08:56+00:00

    I agree with Ken because you can't show such a situation without the junction table. So even if it occurs rarely, if it occurs and you aren't using a junction table, it will be impossible to create the relationship unless you lose the record of the other Supplier.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-04-08T17:52:36+00:00

    With this being a rare occasion would it still be benefical to have that many-to-many relationship?

    Absolutely.  You don't lose anything by it and you can cater for those rare occasions as and when they happen.  In fact, it may seem strange, but there are even occasions when a simple one-to-many relationship type is modelled by a table in this way, and is recommended by Chris Date for those situations.  The reason for doing so is that it avoids having a Null foreign key where a row in a referencing table does not need to reference a row in a referenced table.  In some situations a Null foreign key can be problematical as Null is semantically ambiguous, having no fixed meaning.  The best you can say is that it means something like 'maybe'.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-04-08T17:31:17+00:00

    I understand. I am asking myself that question but I don't seem have a either yes/no answer. I usually stick to the same supplier for reorders, unless I decide to order the same product from a different supplier due to personal or business reasons. This happens rarely, but it does occur. With this being a rare occasion would it still be benefical to have that many-to-many relationship?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-04-08T17:04:01+00:00

    SO then I shouldn't have it as a foreign key in the product column? What exactly are you saying I should do with the supplierID? Keep in mind I still have a SuppliersID table that lists all the suppliers I use.

    As Scott says, it all depends on how you source the products.  I make the point in the light of my own experience.  As it happens, early in my career I worked in the Purchase and Supply department of a manufacturing company, and it would have been unthinkable not to have an alternative source of supply for any item as, if that source failed for any reason, we would have been in trouble.  In our case the range of items bought was extensive, the biggest categories being packaging materials and engineering components, so we were not buying manufactured products for distribution (the raw material for the product was actually purchased at source in other countries).  Your business model may well differ in that there can only be one supplier per product, in which case you'd simply need a foreign key SupplierID column in Products.  You know the reality of the business model, we don't, so only you can determine which is the appropriate database model.

    Was this answer helpful?

    0 comments
  5. ScottGem 68,840 Reputation points Volunteer Moderator
    2013-04-08T15:24:49+00:00

    SO then I shouldn't have it as a foreign key in the product column? What exactly are you saying I should do with the supplierID? Keep in mind I still have a SuppliersID table that lists all the suppliers I use.

    What Ken is saying is that, if you purchase the same product from multiple suppliers, that that is a many to many relationship and SupplierID should not be a foreign key in the Products table. That you need a junction table to model that relationship. 

    However, if you always purchase a product from only one supplier, then SupplierID IS an attribute of Product and should be a FK in the Product table.

    Was this answer helpful?

    0 comments