Assume you want to build a database for some web application. This database already contains many tables and you might have to extend it in the future.
Also, you want the end user to be able to comment any kind of object in the database.
I would like to find a solution for this would be generic enough so that I don't have to extend it each time I add a new table in the database.
I thought of the following:
Table Name: comment
This table sort of solve my problem, the only thing that troubles me is that the relational aspect of it is rather weak (I can't make object_id a foreign key for instance).
Also if some day I need to rename a table I will have to change all the concerned entries in the comment table.
What do you think of this solution ? Is there design pattern that would help me out ?
Isn't that cleaner ?
existing table foo
existing table bar
You are mixing data and metadata, which is not the best design pattern. They should be separated.
However, since the comments don't seem to be very important anyway, you solution is OK. The worst thing you can end up with is to lose comments on your objects.
Some databases, most notably,
COMMENT clause just for the cases like this.
If you want to comment on individual records in each table, it's OK to have such a table.
object_table_name does not have to change if you rename a table, since it's data, not metadata.
You cannot write a native SQL query that will fetch comments for the record of any table (not known by the moment of the query development), though you can build the dynamic queries to do that.
In this case, you will have to keep your data and metadata in sync (
UPDATE the comment table when you
RENAME the table they refer to). The first one is a
DML statement (changes data), the second one is
DDL (changes metadata).
Also make sure that all
PRIMARY KEYs have the same types (same as
Read about EAV. You can make your whole database like that. But then it will be hell working with that data.
Why don't you want to place a Comment attribute for each database entity which should support comments? This way you can get all the data you need in a single query, and many GUI programs for databases will provide you with full code completion in SQL, which will prevent errors that can easily occur when operating with strings. That way the code is heavily dependent on procedural code, which isn't right for database systems.
You can enumerate the table names in a separate table, so that changes in names do not affect the system in any major way. Just update the enumeration table.
Although you are distancing your self from referential integrity, i can see another way to accomplish what you want.
I generally prefer to keep the comments with the rows to which they apply. Assuming your database efficiently stores empty VARCHAR fields, you shouldn't pay a penalty for this. There isn't really anything to "extend" when you implement this approach, the maintenance of the comment becomes part of the queries you are already using to update the rows.
The only advantage to the single-note-table approach is that it allows easy searches across notes for different kinds of database entries.
Assuming MS SQL, and if the volume is relatively small, as you seem to suggest, then Extended Properties might be worth exploring. I've used them sucessfully in the past and they seem to be a permanent fixture.