My previous post, Quick Validation, concluded almost immediately the more abstract model I was considering is not suitable for my purpose here. You might be wondering why.
In a relational database, referential integrity is a critical consideration. Normally a table may contain fields defined as ‘pointing at another table’. These fields are used to connect the tables, and it is crucial that they have only valid links (and possibly nulls, if you can have records that are not linked).
If you can introduce values that are not valid, then queries don’t work and that makes database users and report writers sad. And angry. And possibly questioning their sanity.
Don’t let this happen.
Instead, I’ll go back to my previous model, or one quite like it. I’ve removed the ‘ROLE’-type tables and expanded some of the XM tables to reflect the purpose the roles took. This has a good effect and a bad effect.
One good effect is that each XM table serves a specific purpose. There are multiple connections between Document and Creator, and having them in independent tables means I can treat them separately and possibly have different attributes on the XM tables. When I’m rendering the attributes of the Document (or of the Creator) I can get each field independently, and don’t have to filter them or retrieve them all and separate them. This means less special-purpose code, which is a good thing.
One bad effect is that each XM table serves a specific purpose, and getting the entire set can require multiple queries or a union of the queries. Multiple queries against different tables vs a single query against one table, the single query will almost always be faster. I don’t expect the difference record counts to be large enough to be significant — performance of indexed columns in a good RDBMS is almost better than linear. That is, doubling the number of records doesn’t double runtime of a query, if the query can take advantage of indexes and a good query plan.
This is probably a reasonable tradeoff. This approach does make things slightly more involved if I want to add another type of connection between entities (let’s say I want to add ‘Playtester’ to the Document/Creator connection), in that I’ll end up defining another table instead of just adding another value to the ‘Document_Creator_Role’ table. On the other hand, it makes the definition, and the code to implement, each attribute simpler. Since maintaining code is a headache and extending the attributes is a thing I do infrequently (but still more often), I’ll take simpler code and simpler definitions over ease of insertion.
For now, at least. Once I implement this and try it for real I could well change my mind.
