Database scheme for product tracking

database-design

I'm in the progress of creating a separate login for users who want to track specific product shippings.

So I got the tables:

  • Products (Id, Name, Color, Description, …) Used for storing the products, nothing else
  • ProductTrackings (Id, Name)
  • ProductTrackingProducts (Id, ProductTrackingId (FK), ProductId (FK)) Used for storing sold products that are being shipped and can be tracked
  • ProductTrackingUsers (Id, ProductTrackingId (FK), Username, Password) used for storing the individual users who can track product shipments

But now I'm stuck in how to accomplish my task, the requirements are as following:

  • A single user needs to be able to track multiple (shipped) Products (via the ProductTrackingProducts table, I suppose?)
  • A single (shipped) Product (via ProductTrackingProduct) can be tracked by multiple users

So I have a list of shipped products that are available for tracking (ProductTrackingProducts?) and want n users to be able to track the status.

I suppose my database schema is not sufficient or effective in handling that, so I'm asking for help.

Best Answer

Normally what you would have is something like this:

CREATE TABLE Product (
ProductID,
Name,
Description
) ;

CREATE TABLE Order ( /* Shipped products */
OrderID,
ProductID,
ShippedDate
);

CREATE TABLE User (
UserName
) ;

And here's the essential table you need. This establishes a many-to-many relationship (commonly known as an intersection or bridge table) between Orders and Users:

CREATE TABLE User_Order (
OrderID,
UserName
) ;

For every order you would insert one record into the User_Order table for every user that can track that order. So you would have something like this:

OrderID         UserName
-----------------------------
1               joe
1               jane
1               john
2               joe
3               bill
3               jane
Related Question