Are blank foreign key columns bad?
Is it bad when there are several several foreign key columns in a table but for any given row, only one of the foreign key columns have a value?
I am currently making a nested tree table to create a tree navigation menu. Each entry in my table will represent a node in the tree menu and will be joined to a different table in the database. For example, a menu node that links to a post will be joined to the post table and a menu node that links to a comment will need to be joined to a comment table. Since the nodes will link to many different types of pages, the table for my nested tree will need to contain many foreign keys and for each row all except one of the foreign key columsn will have a value.
I heard that when a table does a join often, it should be indexed, but at the same time, having too many indexes will also create problem. What's the best way to go about my situation? Is there a way to have just one foreign key column that can join different tables depending on the value of another column? This way, no matter how many different types of tables my nodes need to join, I would only have need two columns: one column for the foreign table name and another for the foreign key id.
I am currently using MySQL 5 with the Doctrine ORM.
Thanks for any help!
The db should not allow you to insert a row that is foreign that does not match a row in the foreign table.
So I am assuming your current solution will not work.
If you drop the fk constraint you should be able to enter them as you wish but will be looking at possibly having inconsistant, non normilized data.
best bet is to build a stored procedure in yoour db that will allow you to join only on the 2 tables you need using if statements and not have to have an ungly sql statement that connects to everything.
Thanks for your advice. Could you elaborate on how I can implement your suggestions? So if I were to use a procedure to join the two tables, I would still need to have a column in my category table that gives the primary key of the table that I want to join right?
Hmm, are you saying that a menu entry will link to a specific record in one of many tables? This sounds like a very strange design. Why would a menu entry link to a specific record? Maybe I just don't get what you're up to from your brief description.
Maybe you need to revisit your design, because I think you've painted yourself into an awkward corner.
If you make a separate field for each of many possible foreign keys, yes, you're going to have the problem you describe: a need for many indexes on the same table, and probably a maintenance nightmare. I think darthtampon is incorrect about the legality problem: Databases I've worked with require a foreign key field to EITHER match a record in the target table OR be null. And I think mySQL doesn't enforce foreign key contraints at all: declaring something to be a foreign key is just documention to yourself. Maybe newer versions enforce it. But in any case having a dozen null fields in every record seems pretty awkward.
On the other hand, using a single "generic" foreign key field is also ugly. For starts, this requires the foreign keys to all have the same data type and size. Like, if tableA uses an integer for a key and tableB uses a varchar, how will you put both possible types of value in the same field? Oh, I suppose you could convert all types to character and make it sufficiently big, but this is getting really ugly.
Maybe if you gave a little more detail about what you're trying to accomplish someone (not necessarily me!) could give a better answer.
I am currently trying to build a drag and drop tree tool for general use. Each node in the tree will display the entry information the relevant table. For example, if I have a tree that shows the registered users to my website, by left clicking one of the the nodes, I would get a menu that allows me to see the user's name and email address. The location of each node in the table would be in my tree table, but the user name and email address would in the user table. When the drag and drop tree is used for another application, for example, to show the comments posted for the articles on a website, the nodes in the tree may represent comments. Therefore, by left clicking the node, I would see the comment title and text.
Hopefully this shows, why I feel I need to join two tables; the node information would be in the Tree table and the context specific information would be in the table that is given by the context.
Please let me know if I could more more clear.
Ah, so it's not really a "menu", it's a node tree of some sort.
But hmm, if you're trying to make your code generic, then I think you're pushed to a solution that involves storing table name and key. It's not clear to me why this is going in a database at all, why not just store the table name and key in the HTML, but pursuing that question would surely just get me even deeper into what your application is all about. (Well, maybe you build this big tree once and keep it, rather than refiguring it on the fly with each display. Again, without knowing more, I can't judge the relative merits.)
But my first idea based on your description is that the data you need is a table name and a key field, with the key field defined as varchar and big enough to hold any possible key. If you want to support files with multiple fields making up the key, I'd define multiple key fields.
At that point a foreign key specification is hopeless, which is potentially a drawback to this implementation. Any referential integrity would have to be maintained in code instead of magically by the database.
On the other hand you could index on the key easily -- there's only one. And the table would be small and simple.
The other obvious implementation is what you originally discussed, making a separate field in the "reference" table for each possible destination table. But if you're trying to make this generic, that means the table layout would be different for every implementation, and could potentially have a huge number of fields. What if there are fifty target tables? Well, disk space implications are probably less than one might fear as a null field takes very little space -- I recall reading a discussion on the internals of some DBMS that said a null field took only one bit, in a set of "null field flags" at the beginning of each record. (I forget which database that was. Maybe mySQL. I don't mess with that kind of internal enough to remember which was which until I have to look at it again.) Code to process it would have to loop through all the fields to find the non-null one. You create the possibility of a program bug creating a record with two non-null fields, which is then difficult to interpret.
Frankly, I think both these solutions suck, but I can't think of a better one. If anyone else on this forum has a better idea, I'd be interested in hearing it myself for future reference.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)