1

enter image description here

I'm building a claims database with the above schema so far. Three three-part key on tblPatient is to uniquely identify individual's claim for a certain problemX. The same patientID can appear in tblPatient as long as the admission/discharge dates are different.

This database is also concerned (not pictured) with claims that are NOT associated with problemX. These claims will be identified with another three-part key of patientID, claimsFromDate,claimsThroughDate. So, tblPatient.admissionDate and tblPatient.DischargeDate will not have to be equal to claimsFromDate and claimsThroughDate and if they are equal it's happenstance.

Since tblPatient.patientID is repeated more than once (for those that have more than one visit), I cannot simply copy it to another table without breaking unique constraints for a primary key. I need to be able to relate patientID with the rest of the claims. Do I need to redesign my tblPatient to only have one field as a primary key, or include the already-existing three-part key and just roll with it?

wootscootinboogie
  • 8,461
  • 33
  • 112
  • 197
  • 1
    I hope you never plan to have Virginia hospitals in your database as most of them are in cities and thus not asscociated with a county. – HLGEM Jun 13 '12 at 21:47

1 Answers1

4

First of all: In a perfect, puristic, database-world you would split your patients database into two - one containing the patients, and another called "PatientClaims" or such. If you do not care for patient-specifc data apart from patient-ID, then you should at least rename the table.

That same puristic approach would also tell you the primary key is defined as "The only set of data that uniquely identifies the row" - which is likely your three fields. (I could assume that you could leave out DischargeDate, but only if you are sure that the logic for doing so is sound)

Seeing, however, that you have to work with: 1. A three-part key, which is never pleasent 2. Having that key-combo across two tables, and likely having to join them

I would suggest simply defining a new key - like "ClaimID" using whatever autoincrement functions your database of choice has available.

Unrelated note: Your whole state/county double-table set looks kinda weird - but that might just be me not understanding what you are modelling.

Kerbocat
  • 378
  • 1
  • 9
  • i agree - patient should be about a person. claims should relate that person to an incident or claim. it does look like you should have some more connections between the states and counties. – Randy Jun 13 '12 at 21:10
  • The state and counties actually work out well. I didn't want to have 50 different tables for each state and the statecode gives you the name of the state. The countyCode is a three digit code that signifies a county within a particular state. So multiple states can have `001 Anderson County`. The state code allows you to separate which is which. – wootscootinboogie Jun 13 '12 at 21:19
  • @Kerbocat I figured I needed to add a surrogate key for the claims. The data were given to make in a strange manner and some people in the database moved states so while they had the same patientID, they had a different everything else. In this manner I can also see how their health codes (tblICD) change over time. I'm new to designing databases, self-taught (with the help of SO) and never made anything with CPKs before. Gotta learn some time :) – wootscootinboogie Jun 13 '12 at 21:23
  • I plan on giving you credit for the answer, but could you please explain how this needs to be in two databases. – wootscootinboogie Jun 13 '12 at 21:25
  • I am sorry - I did not mean "two databases" - but "two tables". One table would contain patient-data (Name, next of kind etc.) - the other would hold "patientclaims" – Kerbocat Jun 13 '12 at 21:28
  • 1
    And my thing with the states were mostly due to your having both a "tblStates" and a "tblHospitalStates" table - and I failed to see why those two tables would be different in any way - they would each contain the exact same states wouldn't they? (same goes for the counties) There is nothing wrong with having both the Hospital and the Patient table look up values in the same two tables. – Kerbocat Jun 13 '12 at 21:31
  • You pretty much described what I was thinking about doing. It's a little weird, though because I was given age (not listed) as a hardcoded number, and of course it changes with every visit. I started to run into some problems as soon as I used the CPK in tblPatient. – wootscootinboogie Jun 13 '12 at 21:31
  • They one, but I wanted to avoid using the same lookup table for both of them. This way I can easily say where tblState <> tblHospitalState and the like, basically avoiding the 'one true lookup table` – wootscootinboogie Jun 13 '12 at 21:34