SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB Design Problem

    I've been frustrated over a database design problem, and now that I've torn out half my hair thinking about it, I figured it was about time to ask for some advice.

    So, here's the situation (excuse the length, I have a tendency to babble on a bit).

    Here's the structure of two tables I have in the current database.
    Code:
    Table: egl_products
    
      productid int(11) NOT NULL auto_increment primary key,
      productname varchar(255) NOT NULL default '',
      producttype int(10) unsigned NOT NULL default '0',
      companyid int(11) NOT NULL default '0',
      developerid int(11) NOT NULL default '0',
      productdesc text NOT NULL,
      minspecs text,
      recspecs text,
      publisher varchar(50) NOT NULL default '',
      developer varchar(50) default NULL,
      multiplayer text,
      graphics text,
      releasedate date NOT NULL default '0000-00-00',
      releasedate_approx enum('spring','summer','fall','winter') NOT NULL default 'spring',
      releasedateold varchar(22) NOT NULL default '',
      price varchar(6) NOT NULL default '',
      genreid int(11) default NULL,
      shown tinyint(1) NOT NULL default '1'
    
    Table: egl_producttypes
    
      typeid int(10) UNSIGNED NOT NULL auto_increment primary key,
      typename varchar(50) NOT NULL default ''
    
    Table: egl_articles
    
      articleid int(10) unsigned NOT NULL auto_increment primary key,
      catid int(10) unsigned NOT NULL default '0',
      productid int(10) unsigned NOT NULL default '0',
      titleextra text,
      authorid int(10) unsigned NOT NULL default '0',
      validated tinyint(1) NOT NULL default '0',
      edited tinyint(3) unsigned NOT NULL default '0',
      description text NOT NULL,
      datetime int(10) unsigned NOT NULL default '0',
      titleimageoverride varchar(255) default NULL,
      articlegroup int(10) unsigned default NULL,
      templateoverride varchar(250) NOT NULL default '',
      forceallowcomments tinyint(1) NOT NULL default '0'
    Okay then... in egl_products, producttype is a forreign key for typeid in egl_producttypes.

    Using the present system this works great, however the current system isn't flexible enough. The problem is that this is a site providing reviews/previews of computer games, and many games (products) come out on more than one platform (producttype). In the past, if a game appeared on more than one platform we'd simply create a second version of that product with a different producttype.

    What I'd like to do now is have it so that a product can have multiple producttypes. My initial thoughts were to have a product link table, which linked the product to the type. However this is where the second problem kicks in.

    The second problem is that different games may have different information. Take Halo for example, on Xbox it was released in 2002, whereas the PC version is being released in the next month or so. Suddenly having one product in the database isn't possible since different versions of a product may have different data.

    If anyone has any suggestions or advice, I'd be most grateful becuase I'm out of ideas.

    Ant.
    Regards, Ant.

  2. #2
    Ceci n'est pas Zoef Zoef's Avatar
    Join Date
    Nov 2002
    Location
    Malta
    Posts
    1,111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seems to me that you need to divide the 'products' table into 2.

    A 'products' table that contains all the 'common' data and a 'product_per_platform' table that contains the platform specific data.

    Same should probably be done with the articles table.

    Rik
    English tea - Italian coffee - Maltese wine - Belgian beer - French Cognac


Bookmarks

Posting Permissions

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