Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Oct 2009
    Posts
    11

    Unanswered: Problem with Database Design

    Hi!

    I am a student and I am working on database that concerns customer-order-product relationship. When designing it I encountered an issue that I do not really know how to solve it in the way it seems reasonable and obeys Normalization process. I would appreciate for any assistance.

    Description of my problem:
    We have product for instance T-shirt that can have different sizes and colors and each of this specific combination has quantity (http://img251.imageshack.us/img251/8871/products.jpg)
    Next I created relation between Orders and Products as many-to-many (http://img406.imageshack.us/img406/8...derdetails.jpg). And here problems starts… In Order_Details table if someone order 5x of T-shirt with size S and color blue, and then 3x of the same T-shirt but with M size I will have repeated some values. Another thing is that Size and Color attributes rely only only on ProductFK Key and not the whole Key (OrderFK-ProductFK) so I moved these details to another table like here(http://img18.imageshack.us/img18/350...derdetails.jpg), but now arise another problem Quantity attribute… in my it should be placed in Order_Details, right ? The thing is now I ended up with basically the same table like Product_Size_Color (http://img251.imageshack.us/img251/8871/products.jpg) but without Quantity attribute which we do not need here since I assume it should be in Order_Details table. How I can combine it altogether to get consistent database ?

    Whole database until now looks like this (http://img190.imageshack.us/img190/8...ledatabase.jpg )
    If there is anything else that in Your opinion is bad please let me know.
    Last edited by kkris1983; 10-03-09 at 17:00.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This ... ny (http://img406.imageshack.us/img406/8...erdetails.jpg). An .. and this ... e(http://img18.imageshack.us/img18/350...erdetails.jpg), b ... link give a "404, not found" error.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Posts
    11
    Now it works, my bad sorry

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Kris,

    Your data model is on the whole pretty good.

    Just a few things I would change:
    I think the table names are misleading.
    The table Products should be named ProductGroup (ProductType, ProductClass...) or something like that. It's an abstract class. A "T-shirt" is not tangible, it's more an idea. What does exist is a "yellow XL T-shirt" and that is a product people will order. That's why I would rename the Product_Size_Color table to Products.

    I would also add an extra ID to the (new) Products table, to replace the ProductGroupFK + SizeFK + ColorFK as PK. But this is not mandatory and an issue of some debate.

    Drop the ProductOrderDetails table, I see no need for it. Instead add a ProductFK column to the Order_Details table and a QuantityOrdered column.

    In both your Order and People table are address columns. I would make another table Address containing those.

    I don't quite get the Product_Size_Measurements table. I think you want to define what possible sizes a certain "T-shirt" is produced in. If that's the case, you should drop the SizeFK from the (new) Products table and add a Product_Size_MeasurementsFK column. Otherwise you could erroneously INSERT a "Prada T-shirt" (that only comes in S and L), with size XXXL.

    The Suppliers People relationship is many-to-many. I never design an many-to-many relationship and leave it like that, I always convert it immediately to a cross table, even while designing, because in most cases you want to keep some additional information about this relationship.

    A Supplier can have many People working for them. And a People can work for many Suppliers (in time). At the moment you have no means to make a differentiation among the People belonging to a Supplier. Who is the director, the account manager, the bookkeeper, ... You could add that info to the cross table.

    The problem with such assignments is that you never know when to stop. You could elaborate on the many-to-many relationship between Suppliers and People. With a history of who was the account manager for Supplier X on May the 13th 1986. But this will drive you a long way away from the essence of your assignment, I think. You could turn that relationship into a 1-to-many one, a Supplier can have many People working for them. But then you would have to add a not mandatory column SupplierFK to People and that column doesn't belong in that table (you know why?), but in a separate table, so a People can change employer over time. So we are back to the cross table.

    Edit: I wrote this without looking at the now-working links.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2009
    Posts
    11
    Quote Originally Posted by Wim
    I think the table names are misleading.
    Cannot disagree with You
    Quote Originally Posted by Wim
    The table Products should be named ProductGroup (ProductType, ProductClass...) or something like that. It's an abstract class. A "T-shirt" is not tangible, it's more an idea. What does exist is a "yellow XL T-shirt" and that is a product people will order. That's why I would rename the Product_Size_Color table to Products.
    Thank You for pointing me that, when I read Your reply it bacame obvious

    Quote Originally Posted by Wim
    I would also add an extra ID to the (new) Products table, to replace the ProductGroupFK + SizeFK + ColorFK as PK. But this is not mandatory and an issue of some debate.
    I prefer the way it is right now because that is more readable for me

    Quote Originally Posted by Wim
    Drop the ProductOrderDetails table, I see no need for it. Instead add a ProductFK column to the Order_Details table and a QuantityOrdered column.
    So to make it looks like here http://img36.imageshack.us/img36/193...erearanged.jpg ?? Now it makes sense I really need to focus on giving appropriate names for tables otherwise I will mislead myself again :/

    Quote Originally Posted by Wim
    In both your Order and People table are address columns. I would make another table Address containing those.
    Alright I missed that somehow :/

    Quote Originally Posted by Wim
    I don't quite get the Product_Size_Measurements table. I think you want to define what possible sizes a certain "T-shirt" is produced in. If that's the case, you should drop the SizeFK from the (new) Products table and add a Product_Size_MeasurementsFK column. Otherwise you could erroneously INSERT a "Prada T-shirt" (that only comes in S and L), with size XXXL.
    Product_Size_Measurements table is made for storing specific measurment's length. For example Jeans can have length, and girth, T-shirt can have length of sleeve. That is the Value attribute for and Table Measurments stores the name of specific measurment. Is it a mistake ?

    Quote Originally Posted by Wim
    The Suppliers People relationship is many-to-many. I never design an many-to-many relationship and leave it like that, I always convert it immediately to a cross table, even while designing, because in most cases you want to keep some additional information about this relationship.

    A Supplier can have many People working for them. And a People can work for many Suppliers (in time). At the moment you have no means to make a differentiation among the People belonging to a Supplier. Who is the director, the account manager, the bookkeeper, ... You could add that info to the cross table.

    The problem with such assignments is that you never know when to stop. You could elaborate on the many-to-many relationship between Suppliers and People. With a history of who was the account manager for Supplier X on May the 13th 1986. But this will drive you a long way away from the essence of your assignment, I think. You could turn that relationship into a 1-to-many one, a Supplier can have many People working for them. But then you would have to add a not mandatory column SupplierFK to People and that column doesn't belong in that table (you know why?), but in a separate table, so a People can change employer over time. So we are back to the cross table.
    Suppliers and Customers are in relationship one-to-one(Not like You mentioned many-to-many) because those 2 tables inherit from People. I made this way because both Customers and Suppliers were almost the same except Company attribute that i added to Suppliers. And now when You mentioned about another table Address(or Contact) I could rearange it.
    Last edited by kkris1983; 10-03-09 at 19:29.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The link ...like here http://img193.imageshack.us/img193/9...rearangedf.jpg ?? Now .... gives : 403 - Forbidden
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Suppliers and Customers are in relationship one-to-one
    Ow, OK. My mistake, I'm used reading PowerDesigner models.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Oct 2009
    Posts
    11
    Sorry for that mess I edited that post like hundred of times because I always forgot something now it is the way I like

    That is most important part of it...
    So to make it looks like here http://img36.imageshack.us/img36/193...erearanged.jpg ?? Now it makes sense I really need to focus on giving appropriate names for tables otherwise I will mislead myself again :/
    is it ok ?

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Looks good (Address table?)
    One thought: The added value of the Measurements thing is not clear to me. Is it part of the assignment?

    OK. Now that you're that far, let's take this a little further.

    Your data model is excellent to handle T-shirts. What about paper, charcoal, gasoline, ...? How would they fit in? I think the two property tables Sizes and Colors are too restrictive. Too hard wired. Can you come up with a model that allows you to define what properties a ProductGroup can have? Like I would certainly want to define the Octane rating when ordering gasoline, or the vintage year when ordering a certain wine, or grain-size when ordering sand, ....

    The quantity column will in one situation refer to pieces (T-shirts, bottles of wine), for another property to volumes (gallons, litres, m³, ...), mass (kg, lbs, pound, metric tons, ...), ... how would you have to change your model to facilitate that?

    Perhaps you can even come up with a way to limit the number of available values (like for Size, that would be 'XS', 'S', 'L', 'XL', 'XXL' and 'XXXL') for a certain Propertie or ProductGroup. And making sure the possible values for a Products are limited to the allowed Properties.
    Last edited by Wim; 10-03-09 at 21:01.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Oct 2009
    Posts
    11
    doh, I did not think of this and I like Your idea, as soon as I modify all of it I will post it here. I really appreciate for Your kind assisstance.

  11. #11
    Join Date
    Oct 2009
    Posts
    11
    I tried to go further but I get stuck here...
    http://img104.imageshack.us/img104/5014/problemm.jpg
    So the idea is to make realtionship with the result like table "TABLE" so I tried to make table "PROPERTIES" which stores name of properties like Size, Color etcetera and table "VALUES" that stores specific values for Properties. But then when I tried to gather it alltogether I found it difficult to make it real. Could You please give me any hint ? I drew nine A4's sheets of paper to solve this problem with fiasco

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You have the Properties, Product_Property and Values tables correct. There is just one table missing.

    About the cardinality:
    * There is exactly 1 Products corresponding with a Product_Property and possibly many Product_Property are related with 1 Products.
    * There is exactly 1 Properties corresponding with a Product_Property and possibly many Product_Property are related with 1 Properties.
    * There is exactly 1 Properties corresponding with a Values and possibly many Values are related with 1 Properties.
    * Missing Table is Product_Property_Value. Can you find the correct relationships with the Product_Property and Value tables?

    The Values for Properties Size, may contain "XXS", "XS", "S", "M", "L", "XL", "XXL", "XXXL" and "XXXXL".
    With the table Product_Property_Value you can define that the Product "Prada T-shirt" comes in sizes "S", "M" and "L". While "Big Ben T-shirts" come in the sizes "XL", "XXL", "XXXL" and "XXXXL".

    Perhaps a better name for the table Values is PropertyValues or simply Domain.

    But then when I tried to gather it all together I found it difficult to make it real.
    You have a master-detail situation here: 1 products is related to possibly many Product_Property_Values.
    It is good to check the validity of your database design with real data and find out how you can combine the records in those tables to generate the result you want. But perhaps you were adding complexity by mixing database design with report design. Keep your results simple. Concentrate on the values, not the design.
    I drew nine A4's sheets of paper to solve this problem with fiasco
    I disagree. You have shown that you understand database design and you will get better with more practice. If I were your teacher I would conclude you grokked it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Oct 2009
    Posts
    11
    I could not post it sooner because I am in middle of changing apartment. I am moving to another city today so it may be I will not be online.

    Missing Table is Product_Property_Value. Can you find the correct relationships with the Product_Property and Value tables?

    The Values for Properties Size, may contain "XXS", "XS", "S", "M", "L", "XL", "XXL", "XXXL" and "XXXXL".
    With the table Product_Property_Value you can define that the Product "Prada T-shirt" comes in sizes "S", "M" and "L". While "Big Ben T-shirts" come in the sizes "XL", "XXL", "XXXL" and "XXXXL".
    That is exactly what I did but I did not draw it on that picture before because I thought it is not good since I could not combine it with "Quantity" property. So I reached this point here
    http://img3.imageshack.us/img3/4971/problemzk.jpg
    and since then all bacame difficult. (I assume it is correct since relationship between "PRODUCT_PROPERTY" and "VALUES" tables is like many-to-many so that is why we need "PRODUCT_PROPERTY_VALUE" table, am I correct ?)

    I feel like it must be easy but I just cannot figure it out how to combine "Quantity" which depends on many properties like "Size" and "Color" in our case. I do not see a reason at the moment to have "Quantity" column in "PRODUCTS" table. And the idea is to have it like here...
    http://img3.imageshack.us/img3/9489/quantity.jpg

    By the way... I do not really like having composite keys which I found in our realtionship between "PRODUCT_PROPERTY" and "PRODUCT_PROPERTY_VALUE". Is it ok if I introduce one PRIMARY KEY "ProdPropPK" and FOREIGN KEY "ProdPropFK" like here...
    http://img14.imageshack.us/img14/6561/examplecd.jpg
    ? Should I get rid of composite keys in future or just get use to them if I want to design databases in a good way ?

  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    OK, I noticed only now. In my previous post, replace all occurrences of Products by ProductGroup. You are defining properties and their possible values for a ProductGroup.

    Adjust your model to this. Now find out how to relate a Products to possible multiple ProductGroup_Property_Values. I leave that as an exercise to you.
    By the way... I do not really like having composite keys which I found in our realtionship between "PRODUCT_PROPERTY" and "PRODUCT_PROPERTY_VALUE". Is it ok if I introduce one PRIMARY KEY "ProdPropPK" and FOREIGN KEY "ProdPropFK" like here...
    http://img14.imageshack.us/img14/6561/examplecd.jpg
    ? Should I get rid of composite keys in future or just get use to them if I want to design databases in a good way ?
    First complete your model, then I will come back on this. Keep using composite keys for now.

    If you care about best practices and naming conventions:
    * I would name all columns identical: so no ProductPK and ProductFK columns, but only ProductId in both the parent and child tables. Or simply Id in the parent table and ProductId in the child table. Another issue of debate.
    * Table names. When I was at university, I was taught to use singular words, like Person and Product.
    Recently, the saying goes this may put people an the wrong foot, making them think this table will only hold the data of 1 single person or product. So they name tables by the plural form, Persons and Products to indicate it will store data of multiple persons and products.
    I don't like this naming convention at all. And I don't buy the story singular names will put people at the wrong foot. One seldom creates a table to hold data about one singular object.
    I think this is what they taught you. Have you noticed how many times you diverted from this standard? ProductGroup instead of ProductGroups, Product_Property instead of Product_Properties or should it be Products_Properties? Nah, I don't like it at all.

    By the way, why did you changed the model-type? I preferred your first much more. The new model-type (with example data) obfuscates the design. But perhaps you prefer it because you can see how the data fits in. I lost the big picture with these new models.
    When you integrate what I said Products, ProductGroup, the relation wit Products, ... can you do it in your first model-type? It will make things easier for me.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Oct 2009
    Posts
    11
    I have made changes according to Your advices (name convetnion of Tables and Keys).

    Now I need to make sure we treat "PRODUCT" and "PRODUCTGROUP" the same way, because I am getting confused. According to previous design here
    for instance we have: S, black, model:XXX, Prada, T-shirts (I introduced "model" property because there are many Prada's T-shirts).
    "T-shirt", "model:XXX" and "Prada" details were stored in "PRODUCTGROUP" table but "Size" and "Color" were stored in "PRODUCT" table. So we could have Prada, T-shirt, model:XXX
    -Green, S
    -Green, M
    -Green, L
    etcetera.
    Now we are working on generalisation so both tables "PRODUCTGROUP" and "PRODUCT" can have properties so the design should be following link. Is it correct ?
    (I made changes of relationship on "ProductGroup_Property_Domain" table. It cannot be connected with "ProductGroup_Property" table because it would need "ProductGroupID" and "PropertyID" columns of "ProductGroup_Property" table to be PRIMARY KEYS or UNIQUE and that would not work since we have many occurence of the same ProductGroupID in "ProductGroup_Property" table. I hope it is understandable for You what I just wrote :/)
    If so I will start working on...

    Now find out how to relate a Products to possible multiple ProductGroup_Property_Values. I leave that as an exercise to you.
    Quote:
    P.S.
    If you care about best practices and naming conventions
    Yes I do, I want to learn best practices so I appreciate all advices You give me.
    Last edited by kkris1983; 10-12-09 at 05:04.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •