Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Database Design Question

    Hi all,

    We are developing a simple e-commerce store for a customer in PHP, and we could do with some advice about how to handle the requirements inside MySQL.

    The customer sells mobile phone accessories (batteries, faciers etc).

    Each product can have a small, or potentially massive, range of phone model compatibility. So in the admin area he needs to be able to select which mobile models each product is compatible with.

    On top of this, he also needs to be able to add new phone models, and remove old phone models. So the system needs to be very dynamic.

    On the front end we need to be able to search for products, by the phone model. i.e. I am looking for a 'battery' for an 'Nokia N70'. So there obviously needs to be various relationship in the database, however the dynamic nature of this is more then we used to fulfilling.

    At this point in time we have two tables.
    One contains all the product information:

    fldProductId
    fldProductCategory
    fldProductName
    fldProductPrice
    fldProductDescription

    The other contains the current list of Phones:

    fldPhoneID
    fldMake
    fldModel

    We would appreciate thoughts on the best approach, thanks Ben & Josh

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need something that associates a specific product with a specific phone
    have a look at intersection tables

    many phones have a model number and a name. the name is more targetted at the retail customer the model number at the wholesaler. some phoen shave different names but the same (or similar) model number

    price is alwasy a contentious one, do you only sell in one currentcy

    theres nothing for sales (over time its probably usefull information for your custoer to know who has bought waht.
    so they can identify what sales lines are moving, what are static, where the growth is

    if you store what customers are looking for then you get a flavour of what visitors are looking for.

    just as importantly by knowing what a customer has bought you knwo what else may be of interest to them. eg if they bought a nokia phone, they may be interested in special deals on nokia (or compatable headphones, chargers and so on)

    Im surprised you don't have the URL to product images (that probably needs to be a separate table. if the same image is used for multiple products (say a case suitable for iPhone) then you need to consider whetehr you have an intersection table for that.

    so a table for images (photos, diagrams, instructions whatever), an intersection table which links that image to that product
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    Could you explain how the tables work like you said? I am unable to find a good tutorial on the net. Do you have any recommendations?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

Posting Permissions

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