Database Design Question

bengreen1980 · #1 (**permalink**) 07-05-10, 05:30

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

healdem · #2 (**permalink**) 07-05-10, 05:46

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