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-05T19:43:26+00:00

    Starting my new database. I have a suppliers table. Under the Products table would the SupplierID be a foreign key?

    Products Table:

    ProductID(FK)

    ProductName

    SupplierID(FK)

    Where should my Logo and EventID fields go? in the products table or inventory table? I have a logo field because we have two different logos we use and I want to be able to know which has what on it. I created a seperate event table. Am I suppose to do that?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-04-05T18:41:53+00:00

    Thats for the suggestion, but that is way more advanced than I want to take it.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-04-05T18:27:49+00:00

    Was this answer helpful?

    0 comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2013-04-04T16:33:57+00:00

    Close

    Color Table:

    ColorID          Color

    1                      Red

    2                      Blue

    3                      Clear

    4                      Purple

    ect....

    ColorProduct Table:

    Primary Key   ProductID               ColorID

    ?             Men's Wicking Polo    2

    A Primary Key is not necessary for the ColorProduct Table since its a junction table. You can either set ProductID and ColorID as a composite key or place a unique index on the combination. However, if you want you can add a Autonumber as the PK.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2013-04-04T15:02:08+00:00

    So for the Color table and ColorProduct table would it look like this?

    Color Table:

    Product Key          Color

    1                      Red

    2                      Blue

    3                      Clear

    4                      Purple

    ect....

    ColorProduct Table:

    Product Key   Product Name         Color

    ?            Men's Wicking Polo    2

    what would be the key?  (this is assuming that the polo is blue.)

    I hate saying this but can you please dumb it down some? This is not easy for me to understand without having someone sitting next to me guiding me through it.

    Was this answer helpful?

    0 comments