Database Design Help

[Josh Connerty]

Hello all,

I'm trying to build a system to provide an easy access but very clever service select (sounds confusing, I will explain).

Essentially I'm trying to create a system then will firstly store devices of different types, these types are:

• Device type (iPhone, iPod, iPad)
• Generation (4th Gen, 5th Gen etc.)
• Model (iPhone 4s, iPod Touch , iPad 2 etc)

In addition to this I also need to give optional parameters for instance "colour" or "memory" and then options "black" or "16GB" etc. and as I said these must be optional, for instance an iPhone 3G or 3GS will need the parameter of "Memory" with options "8GB" or "> 8GB".

This is just the begining. I then need some way ofadding a repair to each item all though the repairs are muchly the same for instance "Front Screen" replacement. That's nice and easy and I could do that BUT what I need the system to do is calculate the cost of that repair by finding the correct parts involved in that repair. Each part is model specific.

I have a pretty advanced knowledge of PHP and MySql it's just the design of the table structure that leaves me a little bit cufuffled!

Any help would be greatly appreciated.

Thanks,
Josh.

So what's the hard part you are struggling with. Getting the total costs? Surely each part will have a price then you just add them all up.

I'm assuming that you'll need to connect to a supplier's API or remote DB? You're not going to start storing all possible parts for all possible phones in your own db are you? And then try to keep up to date with price fluctuations? Or are you?

[Josh Connerty]

I don't have access to my suppliers database however each part will be populated from their website listing of that product, so when I add that product to the database I will do so with the website address of that part on my suppliers website. Periodically a CRONJOB will run to keep the table updated with price fluctuations. I'd prefer to have them stored in my database then have to reference my suppliers website every time a users goes to select a repair. Saves page load time.

The issues I am having is how to structure my tables. I get how I can use the information and what not I'm just strugling to figure out how many tables and their relationships to one another.

Thanks for the swift replies.

Josh - I think this will be a major bit of thinking.
Off the top of my head, it would go something like this....

manufacturers (Apple, HTC, Nokia etc)
series_models (iPhone, iPad, iPod, Sensation etc) related to above via "manuf_id"
devices (specific models, e.g. iPhone5s, iPhone5c, iPhone4s - with all the relevant info for that particular phone, e.g. OS, dimensions...) - related to OS by "os_id" - maybe some simple yes/no options included here?
os (iOS [7.x], Android [some version])
options (for things like colour, memory...) e.g.[ option_id, option_type_id, device_id, option_value ] related to option_types
option_types (color, memory, battery...) e.g. option_type_id, option_name
parts (specific parts - possibly with prices)
device_parts (link table for devices and parts)
repair_types (screen replacement, new SIM...??)
device_repairs relate devices and repairtypes with time for repairs [to calculate labour costs]
device_repair_parts list all parts (from parts table) against device_repairs_id

That's as far as my head will get me before I have to resort to pen and paper. Obviously, you'll need some way of integrating all this with supplier data and users/orders.