April 14, 2016 at 9:51 am
I can't decide which way to go with this, so whilst I continue to mull it over I thought I'd post here and get your expert opinions too!
In my application, a user can create a request for one or more procedures - a Procedure Request. Each procedure is of a Procedure Type, and each Procedure Type has different columns associated with it. However, no Procedure Type requires more than a handful of columns to store all of its data.
So, I have a Procedure Request table which stores the data common to all Procedure Requests (date, requester, etc.). What I can't decide is where to locate the columns relating to the child Procedures.
Option 1: Add all of the columns pertinent to all of the Procedure Types to the Procedure Request table. If a certain Procedure Type isn't requested as part of that Procedure Request, the relevant columns will just be NULL.
Option 2: Create a table for each Procedure Type (e.g. ProcedureTypeA, ProcedureTypeB) with the relevant columns and include a foreign key column in each to link them to the Procedure Request table - a one-to-zero relationship, where a Procedure Request record will have zero or one associated records in each of the Procedure Type tables.
Option 3: Create one table in addition to the Procedure Request table (e.g. Procedure), with all of the columns relevant to all the Procedure Types, a Procedure Type column (probably linked to a ProcedureType lookup table) and a foreign key column to link to the Procedure Request table. This seems like the least sensible option, since it's basically just Option 1 but with the table split for no good reason.
Performance doesn't really come into any of this. The database is relatively small and lightly used with hardware which is well-capable of handling the load. Additionally, it's not true to say that some Procedure Types are much more common than others. They're all requested in roughly the same numbers.
April 14, 2016 at 10:49 am
From database design/normalization point of view, all ideas are okay. Multiple attributes that depend on the same key can be combined in a single table or spread out over multiple tables and neither will conflict with any normal form except 6th. (Which in my opinion is not relevant or current RDBMS implementations).
I personally consider the third option to be a bit of a kludge. The first and second are both okay. I would base my choice on the typical usage patterns. If you use the second but have to join the tables back together almost every time, then the first will probably make your queries easier. If on the other hand you normally only select columns that would be in a single table, then perhaps put them in a single table.
Note that having a lot of NULL values in a table will increase its size, unless you define the columns as SPARSE.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply