Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Unanswered: Help on designing a database

    I want to write an application to manage webhosting orders , first I want to know how to design database , its not easy coz we have different products and services

    Suppose , We have some products and services such as SSL Certificates, Dedicated Servers , VPS, Shared Hosting ...
    And each of these has different details and models ,
    And something else that make it more difficult , is that , we have to during order process get some different information from user , for example when the user is ordering dedicated server , we have to show them add-ons and some other services that user can buy with their dedicated server, and the user should also enter some mandatory information for the dedicated server like root password , dns setting and …
    But shared hosting is not like this and we don’t want more information like above from user ,
    Reseller account also need some additional information ,
    One of my friends told me there is a technique for this type of databases he told me you can design a database that is very flexible to add any type of products in that with unlimited columns with different type of data,

    What do you think ? how can I design this database ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search for "subtype/supertype" to see how to design things that are similar but different

    your friend was probably suggesting entity-attribute-value (EAV) design -- don't do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    13
    Thanks ,
    I did search but I didn't understand how does it work , I'm new to that so its a little difficult understanding that technique ,
    can some one explain that for me or give a sample script to see how does that work
    or show me another solution

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all of your orders will have several items in common--
    - customer who placed the order
    - date order was placed
    - salesman assigned to order
    - date order fulfilled
    - et cetera

    these go into a "common" table (this is the supertype)

    each row in the common table will have an identifier (primary key) which is probably the order number

    then each different type of order -- SSL Certificates, Dedicated Servers , VPS, Shared Hosting -- will have unique information which makes sense only for that type, and each of these types will have its own separate table (these are the subtypes)

    each subtype table will use the common identifier as a foreign key

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by 4tech
    One of my friends told me there is a technique for this type of databases he told me you can design a database that is very flexible to add any type of products in that with unlimited columns with different type of data
    Your "friend" is actually the Devil in disguise. Under no circumstances should you lend him money.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2009
    Posts
    13
    Thanks
    The first table is ok Iam using that already,
    But the thing that is not easy to understand for me , is that how to store products and services in DB,
    you mean I have to create an specific table for each type of services and products ?

    we have for example 3 type of services

    Shared Hosting
    Dedicated Servers
    Reseller Hosting

    I create 3 category with above names ,
    each of above services has some sub-services and models , and each models has different details.

    shall I still create dedicated table for each type of services , what about orders because orders are not static and maybe user has to enter some additional information about that specific order where should I store them

    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You could create three tables, one for each service.
    Or you could create one table for services and the attributes that are common to all services, and separate tables to store the attributes particular to each type of service.
    Or you could create a single table that stores all the attributes for each type of services, and leave the inappropriate attributes as null for each record.
    Or you could create a single table storing the common attributes of all services, plus an XML column to store undetermined attributes.
    Your choice will depend upon:
    1) The likelihood that you will offer new services.
    2) How different any new services will be from existing services.
    3) The number of common attributes between services.
    4) The number of distinct attributes between services.
    5) The importance of searching on service-specific attributes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2009
    Posts
    13
    Thanks
    I think its similar to my friend's idea

    I was talking to him , I forced him to tell me exactly what he has done for his project

    He told me you need to create 3 tables ,

    1. Attributes
    2. Products or Services
    3. Att_Prod_Relation

    I should add all of the attributes that I have in the first table
    then list all of the products in the second table
    and link them together in third table

    example :

    Attributes talbe

    Columns : ID - Name

    Values : 1 - CPU



    Products Table

    Columns : ID - Name - Price and ...
    Values : 1 - Dedicated Server - 150 $ ....


    Relation table

    Columns : ID - Product ID - Attribute ID - Value
    Values : 1 - 1 - 1 - Xeon 2.5 Ghz



    What do you think ?

    is this a good solution ?

  9. #9
    Join Date
    Jan 2009
    Location
    NYC
    Posts
    11

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by 4tech
    Thanks
    I think its similar to my friend's idea
    .
    .
    .
    He told me you need to create 3 tables ,

    1. Attributes
    2. Products or Services
    3. Att_Prod_Relation
    Its not at all similar to your friend's idea. In fact, this is exactly the Rudy and I are warning you against.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2009
    Posts
    13

    Thank you all

Posting Permissions

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