Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Apr 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Item can be text or photo

    Hi all,

    I have a database design issue.

    I have one table called page. Page contains pageItems but a pageItem can be a photo, a gallery, a newsfeed, an article etc...

    all of them have different tables. so in pageitem I have no real key to what item it has to show. It has a field that tells me in what table I have to look and a field that tells me what ID to look for in that table.

    I know this is not a normalized mysql database. What can I do to normalize this situation? What is the best solution for doing this. This is like an object oriented databse. photo, gallery, newsfeed inherrit from pageItem. But that does not excists in database design.

    small example

    pageItem
    ---------
    pageItemID = 1
    pageID = 5 (what page do I belong to?)
    itemTable = "newsfeed" (look in table newsfeed)
    itemID = 50 (take the record with ID 50 in newsfeed)

    Kind regards.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I'm a little curious myself on Guelphdad's and OldPedants approach on these inherited type situations (I have something of my own that flows).
    I've been toying with something similar that uses exactly as you mention, inheritance for an object relational type design. And also the same issue I'm having since it doesn't exist in MySQL quite the way I want.
    Code:
                 +-----------------+
                 | Rotation        |
                 +-----------------+
                 | id PK           |
                 | job FK          |
                 +-----------------+
                         /\
                         \/
                        ----
         ---------------------------------
    +------------------+            +-----------------+
    | RotationTemplate |            | RotationCustom  |
    +------------------+            +-----------------+
    | id PK FK         |            | id PK FK        |
    | somedata         |            | somedata        |
    +------------------+            +-----------------+
    Now with mine, its related to scheduling, either a custom rotation scheme, or a templated scheme. The approach I'm sorta leaning towards is a union between the RotationTemplate and RotationCustom tables (since the idea is that the id can only exist in one of the two, but I don't know/care which one), but I'm not sure the right way to control this either - nothing actually stops me from reusing the Rotation.id in both the Template and the Custom tables, other than of course the program itself. This seems to be quite reflective of what you are attempting to do, so I'm hoping one of our SQL masters can comment on how to deal with these types of approaches.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Use an ODBMS instead of a RDBMS. <grin/>

    Well, that's if you can find an ODBMS that won't cost you an arm and a leg.

    One possible "theoretical" way to do this is to use a many-to-many table with FK fields that are allowed to be null:
    Code:
    Rotation: 
        rid PK
    
    RotationTemplate
        rtid PK
    
    RotationCustom
       rcid PK
    
    Connector
       cid PK
       rid FK
       rtid FK NULL
       rcid FK NULL
    But of course even that doesn't prevent an instance of Connector from containing *both* an rtid and rcid. You can think of the Connector as being the "superclass" of the RotationTemplate and RotationCustom "classes". I think it's the closest you can come to modeling polymorphism in a conventional RDBMS. And it's still pure crap.

    So, in practice, I'd go with what you have decided on. If you are worried about duplicate Rotation.id values, you could always create a Trigger that would watch for that and barf on your feet if it found a duplicate.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Thanks mate, I sorta figured thats the approach I would take. I have no problem control this from the application side, it just sorta bugs me a bit that its another one of those 'must choose' situations instead of a 'both work in harmony' situations
    I love your comment on polymorphism in the RDBMS. I lold. Though from a programming perspective, the 'Rotation' would be an aggregation factory, and the 'RotationTemplate' and 'RotationCustom' would be the handler classes, so you were not too far off on that.

    Since I kinda hijacked this with my own code, perhaps the OP could post back if this helped to direct them with their code? Yours and mine differ in that I attack a single point of entry which share a PK from a 'parent' style table. Otherwise, I would expect that with properly controlled keys that you could still get away with just the sibling table structure and a union for the data.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Lord Garfield:

    Another way to do this would be:
    Code:
    pageItem
    ---------
    pageItemID INT AUTO_INCREMENT
    pageID INT REFERENCES page(pageID)
    newsfeedID INT NULL REFERENCES newsfeed(id)
    galleryID INT NULL REFERENCES gallery(id)
    photoID INT NULL REFERENCES photo(id)
    ... etc. ...
    And then you only pay attention to the non-null foreign keys.

    And in some theoretical sense that's more correct. But it's a royal pain in the neck to use. And there's no way to guarantee that a give record won't end up with TWO foreign keys.

    So, yeah, I'd stick with what you have.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Fou-Lu: If you ever get the time and the curiosity, look into some of the ODBMS platforms.

    I use to work here: http://www.objectivity.com
    But their system is way overkill for most people. (The US gov't loves them.)

    I'm not sure what cheap ODBMS's there are out there, if any. Hmmm...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Aha! http://www.db4o.com/DownloadNow.aspx

    Not bad! If you are ready to use ASP.NET or Java, at least.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I may need to play with something like that! I can't see actually using one, I mean all the DBMS systems I've used have all been relational but not object oriented. I fear that the data may frighten me more O.o
    Be worth it even if its a cheapy DBMS just to get used to how they are! Thanks a lot for the link, I'll check them out with Java.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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