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)
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.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
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.
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.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
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.
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.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
Before you post, read our: 
