Table with large number of fields - Refine and implement One to One relations?
-
Hello everyone,
This would be my first post here. I am developing a medical web application. Have started with the database design in sql svr 2005. Apart from the tables for security like User, Role, UserRole, Center etc I have a bigger one a Patient table. I have tried my level best to study the requirements so that I can find some way to split it. The patients have their registration details ID, name, etc of up to 16 fields (unavoidable) plus 54 other fields forming the clinical data, Imaging data, therapy results, Lab results. All these for one particular patient.
Would I be doing a big mistake if i include all the 70(approx) fields in the same PatientRegistration table? Infact I have not done that. I have started creating separate tables for clinical data, Imaging Data, Lab Results and implementing a one-to-one relationship between each of them and the master PatientRegistration table. Kindly correct me if that is not advisable here.
If one to one is OK, then this is how i did that.
example:
PatientRegistration
-------------------
PatientID (PK), Name, Address etc
ClinicalData
--------------
ClinicalDataID (PK), PatientID (FK), Rest of the clinical data ..... (created a separate PK for this since I am using 3 or 4 look up tables for ClinicalData)
ImagingData
-----------
PatientID (PK, FK), Imaging Data...... (I set the PatientID as both the PK and FK, since this table doesnt have to be linked to any Lookup or other tables).
Could any one review this design and criticize me plss 🙂
I appreciate your help..
Regards,
Sebastian
-
Most of what you say is good except for the 1:1 relationship between the support tables and the PatientRegiestration table... should be a many:1. Needs separate PK for each support table to do that...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have a medical software background myself
You definitely should split the data up...1. If you are scanning the patient table for patients with DOB = someDate (silly example) then you won't want to be scanning over all those other fields (presumably DOB wouldn't be indexed, but if you search on it often....)
2. Typically you wouldn't retrieve all of the data at once - why burden the table with discrete sets of data
3. Joining tables in SQL isn't hard
4. Some of the data might become many to one rather than one to one - a design with separate tables would make this transition easier. You may wish to code for this anyway since requirements such as these are liable to change!
5. You won't be ashamed to show your DB to someone else, or have a third party read data from it. This always keeps me in check! You wouldn't believe the number of crappy healthcare databases I've had to read data from
I saw one the other day with fields like procType1, procType2, .... with the cumbersome and bulky where clauses that go with it -
Also... Make sure you index the joining fields to make the joining efficient
Another reason to split the table is that the max row size in SQL Server is roughly 8000 bytes. With all of those fields, presumably several of them being varchar(200) or something, you could hit that limit. If you try to insert >8000 (roughly!) of real data SQL Server simply won't accept it. Not nice to discover once the app's been in production for several months or years.
-
I'd vote with a split based on how often you query/need the data. That's relative. If you're looking up patient name, dob, address most of the time and less often getting details, use some sort of vertical partitioning to make the queries run faster.
Keep in mind that this may mean you insert into a bunch of tables when you create a new patient so the application doesn't break when it scans those other tables. It may not, but it's something to be aware of. I'd also consider sizes, as Ian above mentions. If you have text data, blobs, large varchars, etc. You might want to move them to their own tables.
-
Thank You very much Ian and steve. As Ian said, the requirements keep changing. This time had a meeting where in, with the help of my sspecific questions, it now seems that many of the tables which i intented to take off from the master table, infact has many to one relationships.
Regarding indexing, doesnt sql server set the primary key as a clustered index? So you mean to say that i need to set as non-clustered index, those fields which i use in joins(which is updated mostly)?
-
I've also have a background in healthcare software. You can usually accomplish everything very efficiently with some base line fields in the Patients table (FirstName, LastName, NationalID (SSN), etc) and off-load anything outside the norm (because believe me, your clients are going to be requesting a lot of different things they want to track) in an EAV model (eg PatientAttributes). The same goes for Claims, EOBs, etc.
I spent 7 years doing what you're doing, creating columns, managing client requests for new columns, blah,blah. And I could have sat back and handled those requests with absolutely no code changes if I'd used an EAV model. Live and learn.
By the way, EAV models were especially developed for things like healthcare, GIS, and other such applications with large and varying attributes. It's worth looking into...
-
Hi
Yes be default SQL sets the Pkey as a clustered index.
This need not necessarily be the best index for you.
Study how the data in the table will be used and then decide on your indexes (clustered and non-clustered).
You can create Pkey without it being a clustered index.
"Keep Trying"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply