help with tables and where to put the date

[jeff5656]

I am making many tables in an attempt to normalize my relataional database.  I cannot figure out how to handle the date - the billing data changes each day - not sure where to store this.  Here are my tables:

 

patient_table: has pt_id, patient name, medical record number date of admission etc.

billing_table has: billing_id, HP (enum L,M,H), consult (also enum L,M,H), and some others.

 

Now each day someone will choose the billing level (radio button).  Which table would each day get stored in?  Do I make a new table or add a column to one of the above tables?

If this is unclear let me know.  I have other tables (i.e list of diagnoses, but I left that out).

[premiso]

Does billing data change for each user? If so I think it would be stored in billing_table. And if it changes for each user, add a pt_id to the billing table so you know which patient that particular data belongs to. If you want to keep an audit/not delete billing data when it changes, simply add an active field and set the new billing data to active for that user and the rest to inactive (active = 0).

 

Let me know if that helped or not.

[jeff5656]

Well, each patient has a different billing code (from the billing_table) each day.

If a patient is in the hospital 12/1 thru 12/8 then that patient would have 8 different billing codes, with the appropriate date associated with each.

So how do I address this?  Where do I put the date?  I'm not grasping that part.

[Mark Baker]

Well, each patient has a different billing code (from the billing_table) each day.

If a patient is in the hospital 12/1 thru 12/8 then that patient would have 8 different billing codes, with the appropriate date associated with each.

I'd be tempted to add a third table patient_daily_table with columns for pt_id, billing_date, billing_id

[Maq]

You should gather all the tables, columns, info etc. you think you need, and design your database on paper.  Then use normalization to fix them.

[jeff5656]

Ok this question may be stupid but it is what is making me confused at a fundamental level.

 

How are all of these tables linked?  I know you can join a table in your php code, but I mean how does all the info in all the different tables get linked when you STORE IT so the record is maintained with the right data? I have a patient record so the table with patient name must be the main table?  So where in that table do we link to the other tables so we know what that patient has?  In other words, does the main patient table have to have a separate field for each table (i.e. billing_id from the billing_table)?

[Maq]

Please google primary and foreign keys.

[jeff5656]

"Please google primary and foreign keys."

 

I realize a foreign key links two tables but does the foreign key of table 1 go in a field in table 2 or is there a third table?

[premiso]

"Please google primary and foreign keys."

 

I realize a foreign key links two tables but does the foreign key of table 1 go in a field in table 2 or is there a third table?

 

Foreign key of table 2 is primary key of table 1.