Do I need two seperate tables, or 1?

[Rummy]

Hi all, hoping that I'm not asking too much. I'm currently in the process of trying to re-design a database that I didn't make so it's a bit more normalized, there's lots of redundancy in places and I remember being taught in A-level IT(basically extent of my knowledge plus what I've picked up) that this isn't very good. It basically has no well setup relationships either. I'm using Access and don't know SQL or VBA, hope that isn't going to be an issue.


Basically it's a database for patients and blood results from 3 types of tests. At the moment it has the tables as follows;

Essentially, I have 8 tables that I can think of.

Patients(all their details, contains team, doctor and named carer)
TestType1(Blood level) Results (patient id, result, date)
...Drug table - literally just related to Test1 Results above, drug related to the test. Not sure if needed or could just be placed by a list box in Test1.
Test2 Results(KidneyFunction) (patient id, result, date)
Test3 Results(ThyroidFunction) (patient id, result, date)
Named Carers (Name, e-mail, team base)
Doctor (Name, e-mail, team base)
Team (List of teams - for above two tables and patients)

(see attached pic for a better idea)

My issue is; Every patient(many) has a doctor(1), and every patient(many) has a named carer(1). A named carer will never be the doctor, BUT the doctor CAN be the named carer AND the doctor both(not always). How do I solve the problem without the two tables, or can't I?



Tbh, I actually need more help really if anyone is happy to offer. Looking to put the best structure for my data in place(patients table will need to link to carers, doctors, AND team, but in some ways there's redundancy because their carer/doctor will be in the SAME team, and I don't really know how to pull it through from just their Named Carer/Doctor). My current plan of relationships is in pic as attached;


I did think just about putting the Doctors/Named Carers(as it's all the same details for each) just into one table and going from there, I just wasn't sure how to make sure a Named Carer who isn't a Doctor doesn't get accidentally listed AS a Doctor for a patient. Would it involve a Yes/No field and some validation of some sort?

EDIT: Should I have zipped the png? Apologies for not :x

 

[Alansidman]

First off, here is a link on data base design.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

I am not sure about the doctors and care givers, but you should consolidate the three test tables to one identifying the tests in a field TestType or some other proper name.

 

[Rummy]

First off, here is a link on data base design.

I am not sure about the doctors and care givers, but you should consolidate the three test tables to one identifying the tests in a field TestType or some other proper name.

Thanks for the reply. My current rationale for the tests being seperate tables is that Test Type 1 records more information than the other two, and the other two are unique tests, and the patient won't necessarily have had any or all of the tests. I've gotta head home now because we're shutting but I'll give that a read when I get in. I'm also trying to keep the structure of three seperate tests as a lot has been built on those three seperate tables, though that's probably a terrible reason if it leads to a poor database.

 

[Bryan]

I think you're asking for trouble if you have doctors and care givers in two tables, since doctors can also be care givers. I'd put them into one table and possibly add a field for caregiver_type or something like that if you need to differentiate.

 

[VilaRestal]

Firstly: "A named carer will never be the doctor, BUT the doctor CAN be the named carer" is of course a paradox.

I suggest the best way would be to have a Professionals table with two Yes/No fields - Doctor, Carer and then each of the foreign keys in the Patients table would link to this table.

(Edit: This was written before the other posts appeared - so, in effect, I concur with them.

Also, I would put all the test in one table with a field to show which it is. There could be a common lookup list for test results that each entry in that could be made applicable to only certain tests.)

 

[Rummy]

Firstly: "A named carer will never be the doctor, BUT the doctor CAN be the named carer" is of course a paradox.

Quite! Thanks for pointing it out, I need to get into more of a logical mindset. I think I meant that any doctor CAN be a named carer, but not all named carers can be doctors? Anyhow, I digress.

Thanks to all for the replies(and Alansidman's link was certainly a helpful read), wasn't sure if I should do it like that with just one table but looks like it's decided. I'll collapse the two together tomorrow(db lives at work), how do I ensure only the doctors can be selected as doctors though? Validation or through the linking/definitions of keys? I have no natural keys for the doctors/named carers, or Teams, so I'm just using autonumbers.

Still unsure about putting all the three tests together in one table; what would be the benefit of it for the database? The database's idea revolves around the importance of the three tests, and to keep track of them and when they're done/due; but the issue is that they aren't necessarily all done at the same time and Test 1 holds 2 more fields of data than the other two. Still as I said, not too familiar with proper process of database design(though very familiar with my databases's subject material now), so I'm probably missing something here?

 

[gemma-the-husky]

the tables you do NOT need are the test1. test2. test3 tables

just have a single test table, with a reference number, if you must have one. better to have a date and a test type. - what if you have more than 3 tests .....

 

[Rummy]

the tables you do NOT need are the test1. test2. test3 tables

just have a single test table, with a reference number, if you must have one. better to have a date and a test type. - what if you have more than 3 tests .....

They're for three different types of test though? I left out the details to try and avoid confusion, but they're specific blood tests for specific things, each separate (thyroid function, renal function, and a blood level). The database's purpose is specifically to record these three types of tests, and already each table has at least a thousand records in it.

Is the suggestion to have ALL of the tests in one table, defined by selecting from a lookup(of the three) what type of test it was? What benefit does it offer if it's setup like that?

EDIT:Hmm, I see now I may have caused confusion in trying to avoid such by saying Test 1, Test 2 etc. They're not individual tests, but types of tests.

 

[VilaRestal]

There will be a lot of commonality between the tests (patient, date of sample, date of test, etc) but there may well be a lot of differences. From what I know about medical tests then they would each probably need quite a few of their own fields so it may be best to keep them in separate tables. The 'correct' structure would be to have one master tests table with one to one relationships to the other test tables that contain the fields only applicable to each test. You could put all the fields for each test type in one table and leave null those that don't apply. Whether that's a good idea depends how many of those fields there would be.

The advantage to having them all in one table (either a master table with multiple sub-tables for each test-type's own fields or in one tests table with all test-type-specific fields) is in those situations when you want to run queries that bring all the tests together: e.g. "Show me all the tests this patient has had". This can be done with multiple tables (with union joins) but it's more efficient if it comes from one table already (there will be indexes in place in that table) and it scales up much more easily as tests are added in future.

It's not for a database developer to decide that further tests won't be added in future. And to be honest, even if a customer says there will be no need for other tests, if it's not too much effort to build future-proofing in like this then you should.

 

[gemma-the-husky]

i still think the commonality of tests is much more important than the differences. as far as the patient is concerned, he needs a one to many relation with his tests. differnetiating between tests is a different matter.

if you have three separate tables, you will need to redesign your database continually, whenever you bring in other tests, or even when you want to summarise all the tests for a single patient, requiring union queries and so forth.

i would avoid multiple tables like the plague.

 

[Rummy]

Ah, thanks guys. I see your rationale now. It's a database for lithium monitoring and as I said, it was previously made/designed for the express purpose of monitoring just these three tests as they're the relevant ones and what we collect data for. Its only real direct end user is me(to gather/enter data, then generate and send reports). It wasn't made by me, but by a doctor in what I believe was her spare time. However I want to make it into more of a form that I understand and that is more efficient, whilst leaving it performing a large number of its current functions. My job is actually just to use it, but I've added quite a few bits already that have helped for the task at hand. There's actually nobody telling or asking me to re-design it, but I feel it should be done because I've always noticed a number of issues with normalisation, which I've learnt more about thanks to this thread.

There ARE queries built on all the tables, and I never really got union queries as I don't know SQL. I ended up generally using a main query that pulls what we want re:each test, then another query that uses that base query and other tables/queries where relevant. I know it's probably not ideal, but I didn't have the means at the time to do anything better(and don't now either short of a re-design or learning a bit of SQL for union queries).

A patient will have multiple tests of the lithium blood level(at least once every 3-6 months ideally), and multiple tests of the thyroid funtion and the renal function(both at least every 6 months ideally if not more often). With roughly 450-500 patients in the database, and data going back two years, there are 1000+ of each kind of test in the database already. These are the three measures relevant to lithium as a drug, hence why there shouldn't be more test types required in future and I felt it correct to leave them as separate tables.

I think I understand your reasoning now though and I'm definitely going to look at making the tests all into one table however. It will be a bigger job because it'll change the underlying structure much more and require work on the already existing queries and reports etc. It leads me to possibly one silly question, would the database have any speed difference having one large table with all the tests in it, as opposed to having the three tables with the individual tests in them?

 

[VilaRestal]

The only speed improvement would be where all the tables are queried at once. When just looking at a particular test type then it would actually be a little quicker to have them in separate tables. But speed becomes an issue when there are more than just a few thousand records. The only real issue here it seems is your workload.

If you were starting from scratch then it would definitely be best to have a master test table but as the reason for that is to make your life easier and changing it to that now would make your life harder then no, I wouldn't make the change if I were you.

 

[Rummy]

Thanks, I'll still work to improve the normalisation of the database anyhow. It's quite junior, and really made for proof-of-concept to show it could help in meeting the recommended monitoring intervals of these tests. I wish there was more available to put into the project really as the subject has become quite important to me also, but at the moment it's just little ol' me so I'm making do with what I have.

Thanks again to everyone for all their replies, it's helping me gain further insight into the world of databases!