SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Sep 2011
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Which Is Correct

    Hi, i am creating a small site which sells different types of products (i know there are several free off the shelf sites that are already built, so please do not let this influence your help)

    I am thinking of setting up an ORDERS table which will holds various details about the order and then another table called PRODUCTS which will hold the various specific details about the product itself. Then i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order.

    This is an example of both tables:

    ORDERS
    id
    date
    receiver
    product-id

    PRODUCTS
    id
    product-name
    price

    I have two questions which i hope you can help me with:

    1 - as i mentioned above i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order... how should i name the fields in both tables considering there is an "id" field in both tables and a "product-id" field in the actual ORDER table... i know you can work with 2 tables that have the same field name BUT is it best practice to avoid this... what would you suggest naming all of the above fields...

    2 - i will be selling various different types of products, and all of these products have very different attributes that needs to be stored... so should i just make one big huge PRODUCTS table that will have a field that caters for all of the products and there attributes OR should i create a different table for each product type... for example some products will need a fields called "height", "width", "water-depth" etc... whereas other products do not have any values for these fields and need other set fields...

    Thanks in advance for your help... looking forward to your feedback, thanks...

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    1 - as i mentioned above i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order... how should i name the fields in both tables considering there is an "id" field in both tables and a "product-id" field in the actual ORDER table... i know you can work with 2 tables that have the same field name BUT is it best practice to avoid this... what would you suggest naming all of the above fields...
    Using the same column name in both tables is fine. There's no need to specify the table name in the column name (so not product-name, but name), except for the foreign keys (like productid in the order table).

    But... do all orders contain only 1 product? What if I want to order two or more different products?
    I think you'll need another table here: orderdetails, which will contain a row for each product ordered.

    ORDERS
    id
    date
    receiver
    productid

    PRODUCTS
    id
    name
    price

    ORDERDETAILS
    orderid
    productid
    quantity

  3. #3
    SitePoint Addict
    Join Date
    Sep 2011
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, thanks for your reply... i won't explain everything right now but i will be only selling 3 products and users will only ever need to order 1 of them (i am sure of that)... so would you use a different table for each product or would you put them all in to one table

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oo7ml View Post
    what would you suggest naming all of the above fields...
    exactly the same as the way you have them

    Quote Originally Posted by oo7ml View Post
    so should i just make one big huge PRODUCTS table that will have a field that caters for all of the products
    yes, i would

    there is one more thing... with your current design, each order can hold only one product, but what if someone wants to buy more than one product? you will need a third table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    there is one more thing... with your current design, each order can hold only one product, but what if someone wants to buy more than one product? you will need a third table

    Quote Originally Posted by guido2004
    But... do all orders contain only 1 product? What if I want to order two or more different products?
    I think you'll need another table here: orderdetails, which will contain a row for each product ordered.
    Quote Originally Posted by oo7ml
    Hi, thanks for your reply... i won't explain everything right now but i will be only selling 3 products and users will only ever need to order 1 of them (i am sure of that)..

  6. #6
    SitePoint Addict
    Join Date
    Sep 2011
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post



    thanks guys... so, what do you suggest

  7. #7
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oo7ml View Post
    This is an example of both tables:

    ORDERS
    id
    date
    receiver
    product-id

    PRODUCTS
    id
    product-name
    price

    I have two questions which i hope you can help me with:

    1 - as i mentioned above i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order... how should i name the fields in both tables considering there is an "id" field in both tables and a "product-id" field in the actual ORDER table...
    I would name product-id in ORDERS and id in PRODUCTS the same...ie...something like prodID.

    I don't see the point in having different column names for what is essentially the same data. It can only lead to confusion.

  8. #8
    SitePoint Addict
    Join Date
    Sep 2011
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now... i am getting confused

    What naming mechanism should i use...

  9. #9
    SitePoint Enthusiast Adam Chrapkowski's Avatar
    Join Date
    Sep 2011
    Location
    Poland
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can create one big table for all products or a few smaller tables, depends how flexibly would you like to be.
    Suppose, you would like to be a very flexible, then create the tables product, order, client, group and the tables for each special product.

    product:
    product_id,
    group_id,
    product_name,
    product_price
    // all other attributes common for all products
    client:
    client_id,
    client_name
    // all other client attributes
    order:
    order_id,
    client_id,
    product_id
    // all other order attributes
    group:
    group_id,
    group_name
    // all other group attributes
    And now you can create as many groups like you want by creating an additional tables.
    Query data using JOIN ON(product_id).


    The second dynamic approach is to create tables product, order, client, custom

    product:
    product_id,
    product_name,
    product_price
    // all other attributes common for all products
    client:
    client_id,
    client_name
    // all other client attributes
    order:
    order_id,
    client_id,
    product_id
    // all other order attributes
    custom:
    custom_id,
    product_id,
    custom_name,
    custom_value
    In this model you can create add as many custom fields to any product like you need but queering is a bit harder.

    The last option is to create one big table, which is the fastest and easiest method but least elastic.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by webdev1958 View Post
    I don't see the point in having different column names for what is essentially the same data. It can only lead to confusion.
    I don't see the point in names as orderid, orderdate, orderstatus in the orders table, and clientid, clientname, clientstatus, clientwhatever in the clients table, etc. It's double. It's useless. All columns in a normalized database should be about the table's entity (orders, clients) except for the foreign keys.

    If you want to have a talking column name in your PHP script, you can assign an alias in the select query. No need to mess up the database design for that.

    It might be confusing if you're used to do it differently. It isn't confusing at all if you're used to do it this way. In my opinion of course.

  11. #11
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oo7ml View Post
    Now... i am getting confused

    What naming mechanism should i use...
    There is nothing wrong with the names you have used.

    But for clarity as I described in my last post I would have done something like this -

    ORDERS
    orderID (PK)
    date
    receiver
    prodID (FK)

    PRODUCTS
    prodID (PK)
    product-name
    price

    where PK = primary key

    FK = foreign key

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by oo7ml View Post
    Now... i am getting confused

    What naming mechanism should i use...
    Whatever makes you feel less confused
    No really, in the end it makes no difference. But I prefer the way you did it, like me and r937 already posted.

  13. #13
    SitePoint Addict
    Join Date
    Sep 2011
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thanks all, you have all been a big help... i would like to have the database very clean and precise so i will prob go for your option Guido... but thanks to all of you...

  14. #14
    SitePoint Addict
    Join Date
    Sep 2011
    Posts
    266
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually... how do you think this site operates in terms of table(s) design:

    If you click on "M1 Passenger Vehicle", then "No" you will see that the drop down that displays next is based on your selection in the current drop down... do you think it goes to a different table each time, depending on your selection

    https://www.ros.ie/evrt-enquiry/vrte...execution=e1s2


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
  •