SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Newbie : Trival Database Design

    For instance, a company sells 4 brands (A4Tech, Genius, Samsung, Altec Lansing)

    - Each brand has many categories (mouse, keyboard, monitor, speaker, modem, pen drive, ram....)

    - Each category has many models (eg. mouse category has optical mouse, scroll-ball mouse, cordless mouse... )

    - Each model has many products ( eg. optical model has opticalMouse1, opticalMouse2, opticalMouse3...)

    -------------------------------------------------------
    Therefore, in my database design, I have 5 tables:
    Brands -> BrandCategory (Composite table) -> Categories -> Models -> Products

    I put all the products from different models, categories and brands in the PRODUCTS table.


    The problem is designing the PRODUCTS table. Different products have different descriptions. For example, MICE has color, size, protection, shape attributes, but MONITORS do not.. They share different attributes of descriptions.

    Am i correct if i put all the products that the company selling in the PRODUCTS table? How am i going to solve this problem with the minimum redundancy?

    -----------------------

    I need the solution urgently.. I would like to thank for those taking time reading for this matter, if anyone could help, that's great !! . Anyway, thanks in advance here !

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    minimum redundancy? that's but one of several considerations

    query efficiency is another

    so are maintainability, simplicity, extensibility, et cetera

    my advice: put all the products into one table

    this means you will have a lot of columns, but that's okay, as long as you don't hit the upper limit on the number of columns allowed in a table (which depends on the database, but it's easily somewhere in the hundreds, if not thousands)

    for a monitor, some attributes will be null, and for a mouse, other attributes will be null

    but they will have a lot of attributes in common, nicht wahr?

    focus on the commonality, and do not try to split the different types of product into their own tables at this time, if ever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    minimum redundancy? that's but one of several considerations

    query efficiency is another

    so are maintainability, simplicity, extensibility, et cetera

    my advice: put all the products into one table

    this means you will have a lot of columns, but that's okay, as long as you don't hit the upper limit on the number of columns allowed in a table (which depends on the database, but it's easily somewhere in the hundreds, if not thousands)

    for a monitor, some attributes will be null, and for a mouse, other attributes will be null

    but they will have a lot of attributes in common, nicht wahr?

    focus on the commonality, and do not try to split the different types of product into their own tables at this time, if ever


    Ohh... but if I put all the products into PRODUCTS TABLE, then there will be many attributes (>20 attributes) for each model, each category from each brand... There will be many nulls in the PRODUCTS TABLE. Don't you think is it practical?


    My current solution is using the xml to store the product descriptions for each model/category from each brand. The question is, I seem like storing 1 product record in two different locations - database & xml...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    "There will be many nulls in the PRODUCTS TABLE. Don't you think is it practical?"

    yes, it is very practical

    NULL takes up hardly any space, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    "There will be many nulls in the PRODUCTS TABLE. Don't you think is it practical?"

    yes, it is very practical

    NULL takes up hardly any space, eh


    I get your idea

    But how do i get the non-null field names from the PRODUCTS table by using ASP.NET, because every product has its own attributes (field names), right?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, every product has its own attributes

    but along with other common attributes like the primary key, description, price, etc., every product will have a value in the producttype column

    then your application logic can use IF/ELSE logic to decide which of the other columns (optional attributes which depend on what type of product it is) to work with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, every product has its own attributes

    but along with other common attributes like the primary key, description, price, etc., every product will have a value in the producttype column

    then your application logic can use IF/ELSE logic to decide which of the other columns (optional attributes which depend on what type of product it is) to work with

    I would truly appreciate your helps in this matter... you have give me some idea in designing it.... Thanks a billion ! Maybe I need your guidance next time..

  8. #8
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, with the single product table, would you be making references to other tables?

    Code:
    +----+---------+------+---------+
    | id | product | type | company |
    +----+---------+------+---------+
    |  1 | LilMouse|   1  |    5    |
    +----+---------+------+---------+
    Where type 1 might refer to id 1 on a table that indicates we are dealing with mice, company 5 indicates Samsung on the company table, etc.

    Or would you just (for example) use the word "Samsung" in the company column and have that to use as a key to refer to another table with Samsung's website, company info etc if needed?

    Just curious because using the company name seems more speedy, but that process replicated for each column in a table does seem like it could consume a lot more disk space.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Kuala Lumpur
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samsm
    Rudy, with the single product table, would you be making references to other tables?

    Code:
    +----+---------+------+---------+
    | id | product | type | company |
    +----+---------+------+---------+
    |  1 | LilMouse|   1  |    5    |
    +----+---------+------+---------+
    Where type 1 might refer to id 1 on a table that indicates we are dealing with mice, company 5 indicates Samsung on the company table, etc.

    Or would you just (for example) use the word "Samsung" in the company column and have that to use as a key to refer to another table with Samsung's website, company info etc if needed?

    Just curious because using the company name seems more speedy, but that process replicated for each column in a table does seem like it could consume a lot more disk space.

    my database design is :]

    Brands Table
    -------------
    BrandID
    BrandName
    BrandWebSite
    BrandDescription


    BrandCategory Table
    --------------------
    BrandID
    CategoryID


    Categories Table
    ----------------
    CategoryID
    CategoryName
    CategoryDescription


    Models Table
    -------------
    ModelID
    ModelName
    ModelDescription
    CategoryID
    BrandID


    Products Table
    ---------------
    ProductID
    ProductName
    UnitPrice
    ImagePath
    ImageZoomPath
    Descriptions
    ModelID

    and use xml file to store uncommon/specific attribute of particular product..

    What do you think ?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sam, yes, i would store foreign keys to type and company

    alvin, storing xml is okay, assuming you can always translate it on output
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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