1

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.

2
  • You haven't explain some related entities here and based on the entities you mentioned here I propose the table design. But later I realize that how come user is tracking product? He should be tracking by order right? So don't you have Order entity in you project? If yes then why user will be tracking a product by productID? Relation sheep between order and product also become one to many and the tracking table should have orderID instead productID.
    – Anup Shah
    Oct 22, 2013 at 14:41
  • 1
    I would start by using the standard terms: Shipment and Delivery Dec 21, 2013 at 19:03

2 Answers 2

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
1

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)
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.