#1
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2002
    Location
    Queensland, Australia
    Posts
    827
    Rep Power
    142

    Table structure for extended objects


    I'm curious if there is a pattern for storing programming objects in a database where one class may extend another.

    So, for example, you might have an abstract class for a media that has nothing more than an ID property. Two other classes extend to for Flash media and Text media. These each have different properties, but they still have an ID which must be unique regardless of the type.

    So when it comes to storing these media in the database, it would be kinda ugly to have one single table that describes all possible media types--especially as more and more types are created. It seems better to have a media table that stores an ID and a `type` and then have a separate table for each type.

    But having a separate table for each type won't prevent a Flash media rowing being created with the same ID as a Text media.

    So is there a better DB design that compliments this? Or is what i've described as good as it gets and then code must be used to ensure the same ID is not reused for different media.

    I know I could also add a `type` column to each of the media type tables which can only contain one value, then make that a foreign key... but having a column that can only contain one value seems redundant.
    Ooh, they have the Internet on computers now!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by DrWorm
    but having a column that can only contain one value seems redundant.
    redundant? not really

    see http://consultingblogs.emc.com/david...-Subtypes.aspx
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo