how would you create the database

[chameleon81]

Hi,

I have a problem on how to design the database for the website I m trying create.

I have skins for iPhone,iPod Touch and other iPods.

Not all the skins are compatible with all the ipods.

I also want to track my inventory.

By using php i want to list skins that are compatible with particular iPod

at the same time I should be able to select a skin and see which iPods are compatible with it.

How would you create tables in such a database?

 

[Aea]

A skin with a set column for compatibilities.

 

[angelwatt]

I'd do something like this,

CREATE TABLE skins (id INT UNIQUE NOT NULL PRIMARY NULL, name VARCHAR(60), stock);
CREATE TABLE device (id INT UNIQUE NOT NULL PRIMARY NULL, name VARCHAR(60));
CREATE TABLE compatible (skin_id INT NOT NULL, device_id INT NOT NULL);

// Skins by iPod
SELECT skins.*, device.* FROM skins, device, compatible
WHERE skin_id = skins.id AND device_id = device.id
AND device.name = 'iPod Touch';

// iPods by Skin
SELECT skins.*, device.* FROM skins, device, compatible
WHERE skin_id = skins.id AND device_id = device.id
AND skins.name = 'Ocean Skin';

 

[memco]

Here's my stab:

Table 1: Skins

skinID	skinNameipodID
=========================
1	Blue	1
2	Red	2

Table 2: iPods

ipodID	ipodName
==============
1	1st Gen
2	Nano

Then your query could be something like:

SELECT ipodName, skinName FROM skins JOIN ON skins.iodID = iPods.ipodID WHERE skinName = "Blue"

Angelwatt's is probably a more scalable solution, but I imagine that most skins are only compatible with one or two iPods.

 

[SrWebDeveloper]

And to make the process of creating MySQL tables easier including printing out relationships, searching, testing/building of queries and connectivity to the database - download and install phpMyAdmin which is a GUI web based database manager. It's been around 10 years, well proven and well known, and won countless awards including best PHP software at sourceforge.net for best administrative tool. Use it to simplify doing all that was discussed here.

Download phpMyAdmin here.

Note: It's also good for optimizing and repairing tables, easily adding indexes and complete user/privileges management as well. There is even an option to generate PHP code from a query for novices. Since it's web based, access from anywhere - it supports multiple methods of secure access beyond any security you add (i.e. .htpasswd)

-jim

 

[chameleon81]

Guys, thanks for the replies!