Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Basic Database Design - Options and Attributes

    Hello all,

    Currently working a database design for an application, but I am a bit stuck.

    I am wondering how your table structure would look like with the following requirements :

    There are say 5 different categories of products. Each one has a different set of

    a.) attributes : e.g. clothing - weight, material : wine - colour, year : cement - set time, grade
    Attributes are usually constant and don't vary, yet the column names of each category will
    be different

    b.) options : e.g clothing - sizes, colours : wine - bottle or box : cement - bag or bulk, etc
    Options are mostly variables or a list thereof, again the column names of each category will be different

    Currently I have the table structure as follow :

    Product
    -----------
    productId
    productName
    productDesc

    <Category>ProductOptions
    -----------------------------------------------
    <catgeory>ProductOptionId
    option1
    option2
    option3
    etc.. ( they are not really named option1,2,3)

    <Category>ProductAttributes
    -----------------------------------------
    <category>ProductAttributeId
    attribute1
    attribute2
    attribute3
    etc.. (again, not really named attribute1,2,3)

    Any thoughts would be appreciated,

    -- Pokkie

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    There has been considerable discussion of similar design problems on this list, with some suggesting the attributes be in a single table (keyed by category) and others suggesting multiple tables be used - a separate attribute table for each category. Perhaps searching this group for the previous discussions might be helpful.

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    Ok, thank you very much. Going to perform a search and see where I get.

    However, as a follow up, I would like to show you my current database design and get your comments,
    http://img124.imageshack.us/my.php?i...iter0011so.jpg
    (the blue lines are merely to show the seperation between the tables)

    In this design I have not yet included the "attributes" and "options" aspects.

    The issue that I am thinking about here is whether it is really necessary to maintain 4 seperate tables for the details of each of the "stages" that occur? (initialEnquiry,letterOfIntent, Quotation and Order).

    The image that I posted above depicts the following requirement :

    There are 2 'main' entities namley,

    1.) Buyers
    or customers, people who has an intrest in aquiring a product

    2.) Suppliers
    Companies that have products to sell

    When a Buyer is first entered onto the system, we have the necessary information to create a new record in the InitialEnquiry table. This allows us to list and respond to any InitialEnquiries via
    e-mail, phone or fax. Should the client be intrested in our offering, he would send us a LetterOfIntent, specifying exactly the details of the products that he intents to buy. From there, a quotation is created, pdf generated and sent to the client. When the client is finally happy with the quotation, an actual Order is placed.

    It is necessary to track the path that an order has followed, e.g. InitialEnquiry -> LetterOfIntent -> Quotation -> Order. However, I connected each one of the 4 tables with the Buyer table in order to avoid having SELECT's that span too many tables. The connection point would have been Buyer -> InitialEnquiry, without a link from Buyer -> LetterOfIntent, Buyer -> Quotation and Buyer -> Order.

    An agent is one of our employee's. He is assigned to a main category, such as Clothing or Wine. Each agent have their own set of buyers.

    The Product table stores the various products that can be purchased by an Buyer. A product can be supplied by more than 1 supplier. I thought it best to have a link between Product and InitialEnquiry, as the InitialEnquiry is a more general enquiry, rather than actually going through the list of SupplierProducts. Once the actual letterOfIntent is sent, then it would be linked to a SupplierProduct as it shows that the potential buyer is "serious" about purchasing a product.

    I do realize that it is probably better to start a new thread, but I thought that it might be good to connect this post with my first, as it places the first post in a better context.
    Last edited by pokkie; 03-20-06 at 03:52.

  4. #4
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I took a few minutes to look over your diagram. It seems to me that in similar situations, folks usually have a "transaction" or "orders" table, where each transaction/order is kept and a transaction ID is assigned. The transaction can indicate different stages (e.g., initial inquiry, letter of intent, shipping date). It might be worth looking at the retail data models at this site http://www.databaseanswers.org/index.htm.

  5. #5
    Join Date
    Mar 2006
    Posts
    5
    Well thank you for taking the time to look over the diagram. Much appreciated. You do bring up an intresting point in regards to just letting the 'transaction' have different stages. I presume that should the business require the need for the details to be stored at each stage, that I would still require seperate tables. Or perhaps, can then just have a transactionHistoryTable that stores the transactionId & the details of the transaction, allowing the user to review the history, should the need to. What do you think?

    I have taken a look at the website you mentioned, very informative. Thou to be honest, there are so many customer, order, etc designs to go through that I haven't really had the time to do so yet.

  6. #6
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    Glad the website helped. Regarding stages of transaction, once again we are in the one/many attribute/value table dilemma. Some folks would say to go with a separate table for each stage and others would say to use just one table. I would probably lean towards multiple tables for this application.

    Anyway, best of luck - these types of situations can be sticky.

  7. #7
    Join Date
    Mar 2006
    Posts
    5
    Yes, I tend to agree with having them as seperate tables, seeing as they are distinct seperate entities in the business domain, but the central concern that I have is this :

    If I have 4 seperate tables, each one would be linked to its own set of <StageName>Product table, as illustrated in the diagram. Then adding an "Option" for each one of the stages together with 2 "Attribute" tables (One for the default attributes of the products in a category, and one for the product attributes from the Supplier) for the category is 6 tables, for just 1 category. If the application grows to say 10 Categories, and each category having different columns, then thats 60 tables just for the "Attributes" and "Options". I am not sure how this would affect the performance of the RDBMS and/or the application.

    Ok, with the "Transaction design" I would still have the 2 "Attribute" tables, but then I would only have 1 additional "Option" table, cutting the number of tables in halve. Thou, if I want to store the history of the options, that would add an additional table, bringing the total to 4 tables per Category. So if we have 10 categories, its a 60 table (stage table) vs 40 table (transaction table).

    Any thoughts?
    Last edited by pokkie; 03-21-06 at 03:53.

  8. #8
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    Regarding performance, flat tables may have a speed advantage over multiple tables. However, normalization may have a size advantage. Some suggest emphasizing normalization in data tables and allowing flat tables in query results. Generally, having multiple tables is not a problem - most queries will only involve a subset of these tables. A bad design is certainly a bigger problem than having multiple tables. So, I wouldn't make the number of tables the most important guiding principla.

  9. #9
    Join Date
    Mar 2006
    Posts
    5
    Thank you for your responses

Posting Permissions

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