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. Anonymous
    2013-04-08T15:15:11+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.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-04-06T12:42:35+00:00

    Ok so I have my Product table

    ProductID (FK)

    SupplierID (FK)

    ProductName

    BUT when I try to save it, it says ' Index or primary key cannot contain a null value'.

    The ProductID column should be the primary key of the Products table, and, as Scott says, should be an autonumber.

    In tables which model a many-to-many relationship type on the other hand, like the ProductSuppliers table I described above to cater for a scenario where a product can be sourced from more than one supplier, the two foreign key columns are a composite key.  If you were to introduce an autonumber column into that table as the primary key, however, the two columns are still a key as a table can have multiple keys, each known as a candidate key.  It is imperative that all candidate keys, whether a single column or multiple columns be each included in a unique index therefore.  This prevents the inadvertent insertion or duplicates which would undermine the integrity of the database.  Making one or more columns the primary key automatically creates a unique index, so there is no need to do so independently of this with regard to that column or columns.

    Was this answer helpful?

    0 comments
  3. ScottGem 68,840 Reputation points Volunteer Moderator
    2013-04-06T01:50:23+00:00

    BUT when I try to save it, it says ' Index or primary key cannot contain a null value'.

    Right, A PK has to have a value. Wouldn't make sense any other way. That's why an autonumber works so well as a PK since its automatically generated.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-04-05T21:29:39+00:00

    It's also theoretically possible that there could be a many-to-many relationship type between products and suppliers, if a product can be sourced from more than one supplier.  If this is the case you would not have a SupplierID column in products, but the relationship type would be modelled by a table like this:

    ProductSuppliers

    ....ProductID (FK)

    ....SupplierID (FK)

    Was this answer helpful?

    0 comments
  5. ScottGem 68,840 Reputation points Volunteer Moderator
    2013-04-05T20:00:00+00:00

    Yes Supplier is an attribute of Product so its should be a FK in the Products table. Same thing with Logo. However, Event is an attribute of when the product is used. It would also be a many to many relationship. An event can have many products and products can be used at many Events. So you would need a junction table to manage that relationship.

    Was this answer helpful?

    0 comments