how would you create the database

Discussion in 'Web Design and Development' started by chameleon81, Jan 26, 2009.

  1. chameleon81 macrumors 6502

    Joined:
    May 16, 2006
    #1
    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?
     
  2. Aea macrumors 6502a

    Aea

    Joined:
    May 23, 2007
    Location:
    Denver, Colorado
    #2
    A skin with a set column for compatibilities.
     
  3. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #3
    I'd do something like this,
    Code:
    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';
     
  4. memco macrumors 6502

    Joined:
    May 1, 2008
    #4
    Here's my stab:

    Table 1: Skins
    Code:
    skinID	skinNameipodID
    =========================
    1	Blue	1
    2	Red	2
    
    Table 2: iPods
    Code:
    ipodID	ipodName
    ==============
    1	1st Gen
    2	Nano
    
    Then your query could be something like:
    Code:
    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.
     
  5. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #5
    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
     
  6. chameleon81 thread starter macrumors 6502

    Joined:
    May 16, 2006

Share This Page