Database scheme for product tracking

Question

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.

Answer 1

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

Answer 2

I did not get what is the table ProductTrackings (Id, Name) is for but Based on what you explain in requirement you need many to many relation for ProductShipping and TrackingUser.

Note that for the same product you might have multiple TrackingIDs (examples might be Original Shipping, ShippingLost and New Shipping, Prepaid Return Shipping etc….) But for a given time only one will be valid. so TrackingIDVersion is added to just indicate MAX value of that is the most active one. There are better alternatives as well but just wanted to point out that product to Tracking is One to Many relationship.

Here is the possible table design:

    CREATE TABLE ProductDetail
    (
        ID      INT NOT NULL IDENTITY
        ,Name   sysname NOT NULL
        ,color  sysname NULL
        ,SIZE   sysname NULL
        ,CONSTRAINT xpk_ProductDetail PRIMARY KEY (ID)
        ,CONSTRAINT uq_ProductDetail UNIQUE (Name)
    )

    CREATE TABLE ShippingDetail
    (
        ShippingID          INT NOT NULL IDENTITY
        ,ProductID          INT NOT NULL
                            CONSTRAINT xfk_ShippingDetail_ProductDetail 
                            FOREIGN KEY REFERENCES ProductDetail (ID)                       
        ,TrackingID         INT NOT NULL
        ,TrackingIDVersion  INT NOT NULL
        ,CONSTRAINT xpk_ShippingDetail PRIMARY KEY (ShippingID)
        ,CONSTRAINT uq_ShippingDetail UNIQUE (ProductID,TrackingID,TrackingIDVersion)
    )


    CREATE TABLE UserDetail
    (
        ID      INT NOT NULL IDENTITY
        ,NAME   sysname NOT NULL
        ,pass   sysname NULL
        ,CONSTRAINT xpk_UserDetail PRIMARY KEY (ID)
        ,CONSTRAINT uq_UserDetail UNIQUE (NAME)
    )

    CREATE TABLE ProductTrackingUsers
    (
        UserID          INT NOT NULL
                            CONSTRAINT xfk_ProductTrackingUsers_UserDetail
                            FOREIGN KEY REFERENCES UserDetail (ID)
        ,ShippingID     INT NOT NULL
                            CONSTRAINT xfk_ProductTrackingUsers_ShippingDetail
                            FOREIGN KEY REFERENCES ShippingDetail (ShippingID)
        ,CONSTRAINT XPK_ProductTrackingUsers PRIMARY KEY (UserID,ShippingID)
    )