The following is a rather detailed description of an application I'm putting together. I have a number of options of how to do the representation here and I'm unsure which one to choose even though I have weighed some of the advantages and disadvantages of each. I've come across this sort of problem a few times and think it would serve as an excellent discussion point.
I'm currently trying to represent a navigation system in a database (tree structure using the adjacency model) so I can dynamically create menus on the fly. I wish to make this extensible so that in the future, different types of menu items can be created, each with datatypes specific to that menuitem class. Examples of menuitem classes are currently: Label, Link, Separator, Section. (Link has data DisplayText, URL, Target associated with it, Label would just have LabelText).
Menuitems have one parent and can have many children. Currently, it only makes sense for Section items to have children.
I have a few options for database structure. The first table provides a base for each option.
Table menuitems:
id - primary key
menu_id - foreign key to another table (which menu this item belongs to)
pid - foreign key to id (parent id)
class - Label, Link, Separator, etc.
sequence - order in which to display the items
Now, each class of menuitem has 'extra' data associated with it (i.e. Link has DisplayText, URL, target). There are a few ways I can do this, so I'm going to lay out my options now.
Option 1: Table for each Class
I can make a separate table for each class of menuitem, an id field that is a foreign key to the menuitems id field, and a column for each piece of data that is associated with that class. When someone in the future is adding a new option for the menu, they need to add a new table name and modify the code. i.e.:
table menuitems:
id, menu_id, pid, class, sequence
table menuitems_link:
id, displaytext, url, target
Advantages - Data is not mixed together, it is separated out into individual tables.. so menuitems_links will only contain links. Also, the ability to store data in its datatype form (DATETIME for date data, TEXT for long text entries, TINYINT for small numbers)
Disadvantages - If I want to grab all the items for particular menu, I need to left join on each and every possible menuitem class table. I don't know how efficient that is (and thats alot of NULLs), and I know mysql has a limit of about 20joins or so in a single query... so that caps my number of menuitem classes.
Option 2: Table for menuitems, table for menuitem data
I can make two tables, one for the common data, and one that will contain an entry for each piece of specific data to the class.
The second table would look like this:
id, menuitem_id, var_name, data
So a sample entry for a Link menuitem would have 3 rows:
37, 1, displaytext, 'MyLinkText'
38, 1, url, 'http://www.google.com/'
39, 1, target, NULL
Advantages - Only need two tables always. When someone adds a new class, it still just gets added to the menuitem data table.
Disadvantages - The DATA table column can only be one type... so I'll be storing all data as BLOB/TEXT. So even tiny integers, dates, etc will be TEXT. Also, When I do a select to get all menuitems for a menu... I'll get multiple rows for each menuitem and I'll have to parse through that and see where one menuitem starts and where one ends.
Option 3: Store Data as XML
In this option, I would only use one table. It would contain a field that would have an XML representation of the data for the classtype. i.e.:
<data>
<displaytext>MyLinkText</displaytext>
<url>http://www.google.com/</url>
<target></target>
</data>
Advantages - One table, when extending classes, just a new schema has to be defined.
Disadvantages - Lose searching capability, XML needs to be processed to gather information about it. If I modify a class (say by adding a newwindow flag to the Link class)... I need to select, modify, and update every single entry of class Link to the table.
Option 4: Storing Data as Serialized PHP Object
My final option is to do something similiar to the above, but instead of storing as XML, storing it as a serialized PHP object. I'll most likely be turning the data I retrieve into objects anyway.
Advantages - Data is in object form already when I retrieve it from the db. Still a single table. Extending just means creating a new object that derives from the base object class I create.
Disadvantages - If I lose or change the object definition, it will be somewhat difficult to actually pull the data out of the database. Also, updating is a bit of a problem as in XML.
==
So those are the four major options that I see. There are also some minor extreme ones such as either a) setting up 5 columns in one table for data... thus setting a max to 'extra' data. b) Setting up a table or column for each data type in mysql (DATETIME, TINYINT, INT, TEXT, VARCHAR(255), ...).
In the end, what I'll most likely be doing is taking the data gathered from the database and turning it into an XML representation, that way the same data can be shown in a variety of forms using XSLT or a php parsing function.
I'd like to know what opinions ya'll have. Which method you'd go with and why. And if anyone can think up any better solutions, i'd love to hear 'em.
