none
I need help noodling through a design problem.

    Întrebare

  • Hey everyone.

    I'm stuck on a design issue and I was hoping someone might be able to offer up an idea.

    I'm developing a website in which the users can order products.  The products are defined by the administrator based on the ProductCategoryTemplate.  This ProductCategoryTemplate can also be used by the user to create their own Product for purchase.  The problem I'm having is, on the SalesOrderDetails (which would use the ForeignKey to a specific Product for purchase), how would I link to Admin-defined and User-defined Products?

    Some Backend Design Details:

    A Product can have many Parts (from the Parts table).  Each Part has it's individual properties like size, weight, purchase cost, inventory quantity, etc.

    Product and Part have their respective category tables, ProductCategory and PartCategory.  This is so these items can be grouped, but most importantly, that Parts can be grouped; ie. "A teddy bear is made of Filling, Skin, Eyes, Accessories" - Each of the bold items are PartCategories, where each PartCategory can have many Parts.

    The ProductCategoryTemplate is a table which defines a 'Teddy Bear', by way of a M2M relationship with the PartCategory.  This will enable the administrator to define what makes a Teddy Bear.  Then, in the administrative back end, if the Admin wants to produce a new Teddy Bear, the application will know which Parts to serve to the Admin for the new Product.  The admin's choices are recorded in the Product table (by way of linking the Product to the Parts) and the new Teddy Bear will show up in the store front.

    If a user were to order a pre-defined Product then I would link the SalesOrder (by way of SalesOrderDetails) to the Product. That is very straight forward.  But...

    A user can use ProductCategoryTemplate to create their own Teddy Bear, too!  In much the same way an Admin will add a new Teddy Bear to the store front, a user will get the same design form so they can build their own bear. -- This is where I'm confused.  Do I take this information and just store the user defined Product in the products table?  It won't have the same attributes as an Admin defined bear, like ProductName.  Additionally, it could be possible for two users to create the same Bear; Skin A, Eyes B, Filling C, Accessories D, E and F.  I feel like it would be good design to have a separate table for UserProducts, which would be clearly separated from Admin defined Products and would allow for duplicate builds.  If I were to have both UserProducts and Products, how would I link each to SalesOrderDetails?

    I've been stuck on this design issue because my database design classes are telling me to Normalize this to not allow for duplicates... but my gut is telling me to just do two tables... but then my limited knowledge is inhibiting two tables since i don't know how to link them to one SalesOrderDetails table.  :X

    Thanks in advance.

    11 aprilie 2012 14:22

Răspunsuri

  • I think your database design classes are telling you the same thing as your gut, just slightly different. What I would suggest is a subclass.

    So you would have a Product table that is a generic product that covers both cases.  It would be simple, like:

    Product (ProductId (PK), ProductNumber (AK), ProductType, GeneralDescription)

    Then two more tables, one for the well defined system product, and the other for the user defined:

    StandardProduct( ProductId (PK,FK), SKU (maybe), standard details)

    UserDefinedProduct (ProductId (PK,FK), details, possibly flexible schema like XML)

    the only question I have is whether the User Defined product is just a configuration of products that could really ust be sold as a product.  So in that case, rather than having two tables, you have a product table which contains FullyCreatedBears, and then "ShellBears".  The Fully created bears would then have individual parts associated with them, so when you choose one it automatically fills in the "parts" of the bear (and proabaly hides the details from the user.)  The consumer configured bear would, after you ordered it, then have choices for the standard parts you need.

    Just a couple of ideas... I think the first idea is greatif you want to beable to re-sell the bear to others, and the latter idea is better if you just want them to be able to build a bear and purchase it, but not really set it open for reuse.

    Interesting problem, will be interested to hear if I am close to what you are looking for/do :)


    Louis

    11 aprilie 2012 20:03
    Moderator

Toate mesajele

  • I think your database design classes are telling you the same thing as your gut, just slightly different. What I would suggest is a subclass.

    So you would have a Product table that is a generic product that covers both cases.  It would be simple, like:

    Product (ProductId (PK), ProductNumber (AK), ProductType, GeneralDescription)

    Then two more tables, one for the well defined system product, and the other for the user defined:

    StandardProduct( ProductId (PK,FK), SKU (maybe), standard details)

    UserDefinedProduct (ProductId (PK,FK), details, possibly flexible schema like XML)

    the only question I have is whether the User Defined product is just a configuration of products that could really ust be sold as a product.  So in that case, rather than having two tables, you have a product table which contains FullyCreatedBears, and then "ShellBears".  The Fully created bears would then have individual parts associated with them, so when you choose one it automatically fills in the "parts" of the bear (and proabaly hides the details from the user.)  The consumer configured bear would, after you ordered it, then have choices for the standard parts you need.

    Just a couple of ideas... I think the first idea is greatif you want to beable to re-sell the bear to others, and the latter idea is better if you just want them to be able to build a bear and purchase it, but not really set it open for reuse.

    Interesting problem, will be interested to hear if I am close to what you are looking for/do :)


    Louis

    11 aprilie 2012 20:03
    Moderator
  • Sorry for the long delay in response, Louis.

    I think you have the right suggestion with both StandardProduct and UserDefinedProduct linking to the PK of Product.  That would link a Product record to either one of those tables, but not both.  One of the features I want to add would be, "Customize this Product", in which the user can take an existing product and just change things around... essentially creating a custom pre-defined product.  To accomplish this, I'd just copy the parameters from StandardProduct to UserDefinedProduct and let them make changes.

    In the spirit or normalization... if User X and User Y both create their own custom product, with all the same specs, would you suggest their respective Product records point to the same record of UserDefinedProduct?  If so, if User X wanted to edit their product, would I just copy the record (so I'm not changing details to User Y) or would it be better to just let two duplicate records (minus the PK FK) exist so I don't have to worry about it?

    Thanks for your input and, again, I apologize for the delay in giving you Answer kudos.


    29 aprilie 2012 00:09