Table question

[jeff5656]

Lets say I have a database with patient name, diagnosis and date of diagnosis.  Lets say the diagnosis can change every day.  This means that for that ONE patient record, there can be 20 or 30 or more different dates and diagnoses, but all associated with the same patient.  How do I set up the tables? Thanks. I'm trying to understand working with multiple tables but can't seem to get past this yet...

[bluesoul]

Without looking at the normalization you would just need to keep your patients in one table, and your diagnoses in the other, and you join the two on a field called patient_id.  This field would be an auto-incremented number in the patient table.

 

That would be a start anyway.

[jeff5656]

But where would the date field go in your example?

[Yesideez]

Actually I'd have two tables.

 

1. Table containing the patient details (name, address etc.)

2. Patient diagnosis etc. of each visit (linked to table 1 by patient ID)

 

Then any data that duplicates itself you can look at normalisation.

[bluesoul]

But where would the date field go in your example?

 

It would go in diagnoses.  Each row is a separate diagnosis but will be joined to a patient via that patient_id key.

[jeff5656]

I may have dyslexia because I can't udnerstand something now.  What if the patient has more than one diagnosis on a given date?  How can you store more than one diagnosis.  If I had a THIRD table with a list of all the diagnoses, how would I store the 3 diagnoses that the patient has on that date?

[premiso]

You only need 2 tables. That is the reason you have the diagnoses table so you can have multiple entries for one user. The date field is no unique and nor should it be. The only part about the diagnose table that should be unique is the diagnoseid. This will prevent errors from coming up with multiple dates for 1 user or the same dates entered twice for one user.

 

That is how it should function, 2 tables are all you need. Stop confusing yourself with thinking about a third table.