sql - Modeling products with vastly different sets of needed-to-know information and linking them to lineitems? -
I am currently working on a site that sells different types of products that are custom-made. I've got your normal, standard cart schema: There are many line items in the order, line items have a product, but I've run into a sticking point:
We say that from our products One is a ball, and one of our products is a box of crayons when people are making their order, we end up making things that can be presented by some Piedocode:
< Code> Ball: Attrib Ute: Diameter: Decimal Color: Foreign_Fronto colors.id Contents: CrayonBox to Foreign_ref Materials.id: Features: Width: Decimal Height: Decimal front_text: String crayons: crayon with many_to_many ...< P> Now, these orders are created and stored in our DB before creation. I can make it very easily so that when an item is added to a car, we get the name and value of the product by linking it to
Ball or CrayonBox in our controller It's creating LineItem , but if we could provide a complete set of information for every line item, it would be good. I have thought about some potential solutions, but none looks ideal:
One: Use an intermediary "product information" linking table, and its references Represent different products, so we will have something like this:
LineItem information: many_to_many with product_information ... product_formation: lineitem: line_item with name many_to_many: string value: string product information (Name = 'color', value = $ SOMECOLOR) Product Information (NO = 'Color', value = $ SOMEOTHERCOLOR) ... The problem with this is that type of data needed for each attribute of a product Should not come under the same column type. I can represent everything with stars, but $ DEITY knows that I can not even come near to think that this is a good solution .
The second solution is to think that each table in the LineItem table is a foreign key that represents a product type. Unfortunately, this means that I have to check the existence of each foreign key in my controller, I do not like it at all, but I like that filling each piece of data in a datatype and then all conversions outside of DB Should handle the luggage.
In another possible solution column, to store tablename of product data, but this is probably not a good thing, is it can do? I lose the capacity of DB to link content together, and it hits me equally to use eval () where it is not needed - and we all know that eval () is not really needed very often
I want to be able to say "Give me the line item, and then expand the information of that line item", and various The right set of information for product types is.
So, those people who really know what they are doing with the database schema, what should I do? How should I represent it? It seems that this would be a very common case, but I can not find much information with googling - is there a similar pattern for such things? Need more information? This may not be beyond the scope of "You can use RDBMS for it", can you do this?
EDIT: Now I'm pretty sure what I want here. With the nickname in my individual models, make the link "normal" after each "Information" table for each product type. Unfortunately, the ORM I'm just using for this principle (theory 1.2) does not support class table inheritance. I can be able to achieve something similar to the "column collection" principle of principle, but still do anyone think that I bark the wrong tree? I looked at EAV, and I do not think this problem fits quite well - every set of information about different products is known, although they may be very different from product type A to product type B. EAV's flexibility might be good, but it seems like the abuse of DB.
It kills me that it is the perfect fit for choice / which is different for each 'row' Allows to include attributes, yet gives indexed lookup permission.