Splitting one table into 3 related tables

[RainLover]

24 Might be a bit too many.

Can you post a Pic of your relationships.

You may need someone to comment before you get into a mess. That is if that is the way you are going. I am really just taking a guess, but better to be sure than sorry.

 

[pivottable]

2 pictures posted:

1. One to many relationships
2. Many to many relationships with use of junction tables, all of which are with tblDeliveries. These are to replace multivalued fields in the original single table database.

I think there are two tables that could probably be replaced with a custom Boolean field in the respective table, tblGender and tblAnaesthetic, as there are only two rows in these tables.

 

[RainLover]

2 pictures posted:

1. One to many relationships
2. Many to many relationships with use of junction tables, all of which are with tblDeliveries. These are to replace multivalued fields in the original single table database.

I think there are two tables that could probably be replaced with a custom Boolean field in the respective table, tblGender and tblAnaesthetic, as there are only two rows in these tables.

I don't know for sure if this is all correct. It does however look as though you have taken notice of others and designed something that looks right.

My suggestion is to move on to the next step and fine tune as you go.

 

[plog]

I'm pretty sure its not correct. I think you went normalization happy and made too tables. I'd have to see sample data from all of them, but I'm pretty sure I can identify a few you went overboard on:

tblGender - unless you're allowing all the new pc sexual identification types that seem to be en vogue (transgendered, female-identified, eunuch, etc.), then you don't need this table, just store "M" or "F" in tblBabies.

tblBreastfeeding - My guess is you have two values in this table, Yes and No. If that's the case, just use a Yes/No datatype instead of this new table.

Those are the 2 I can easily spot, the rest I would need sample data. In general anything with just 2 options should just be a Yes/No field instead of a table.

 

[pivottable]

I'm pretty sure its not correct. I think you went normalization happy and made too tables. I'd have to see sample data from all of them, but I'm pretty sure I can identify a few you went overboard on:

tblGender - unless you're allowing all the new pc sexual identification types that seem to be en vogue (transgendered, female-identified, eunuch, etc.), then you don't need this table, just store "M" or "F" in tblBabies.

tblBreastfeeding - My guess is you have two values in this table, Yes and No. If that's the case, just use a Yes/No datatype instead of this new table.

Those are the 2 I can easily spot, the rest I would need sample data. In general anything with just 2 options should just be a Yes/No field instead of a table.

tblGender: Only M or F! Can I use a Combo Box in an form to allow only those options?

tblBreastfeeding: The 3 options are Yes, Delayed and No. I suppose that is more accurately described as Immediate, Delayed and No, but that was the requested nomenclature...

 

[pivottable]

I am wondering if I have gone normalisation crazy!

For tblLabType, I have 3 options:
None
Spontaneous
Induced

However, under Spontaneous, there are 2 optional suboptions, where multiple selection is allowed. That is to say between 0 and 2 options can be selected.
ARM
Oxytocin

And for Induced
PG
ARM
Oxytocin
Mechanical

In the original table, I had 3 multivalued fields:

LabourType (None,Spontaneous,Induced)
SpontOpt (ARM,Oxytocin)
IndOpt (PG,ARM,Ocytocin,Mechanical)


Is there an alternative solution to creating more many to many relationships?

As 2 out of 4 options are the same (ARM,Oxytocin), can I have one options table, but only allow PG and Mechanical if the LabourType=Induced?

 

[plog]

I'm not familiar enough with your data, but it seems all of these fields could be handled with one additional table. It sounds like all these fields are different types of situations that can occur and then the action the doctor took. If that's anywhere close to the case, I would take all those fields and put them in another table like so:

DeliverySituation
SituationID, DeliveriesFK, SituationType, Action
1, 41, LabType, Spontaneous ARM
2, 44, Induced, Mechanical
3, 44, Induced, Oxytocin
4, 51, LabType, Spontaneous Oxytocin

Again, I don't know your data well enough, but I'm guessing all of these fields can be put into one table.

 

[pivottable]

I'm sorry that my explanation hasn't been very clear.

The table is trying to record the different types of labour a patient may undergo. If I leave the None option alone for a moment, that leaves us with Spontaneous (goes into labour by herself) and Induced (started off).

A patient in Spontaneous labour may subsequently require one of two interventions, ARM or Oxytocin. Or she may not require any of them.

An Induced patient may be induced with 1-4 of the options, PG, ARM, Oxytocin, Mechanical.

So all the permutations are:

Spontaneous
Spontaneous ARM
Spontaneous Oxytocin
Spontaneous ARM Oxytocin

Induced PG
Induced ARM
Induced Oxytocin
Induced Mechanical
Induced PG ARM
Induced PG Oxytocin
Induced PG Mechanical
Induced ARM Oxytocin
Induced ARM Mechanical
Induced Oxytocin Mechanical
Induced PG ARM Oxytocin
Induced PG ARM Mechanical
Induced ARM Oxytocin Mechanical
Induced PG ARM Oxytocin Mechanical

I need to count how many women underwent Spontaneous and Induced Labour every month, and the different sub-methods may be studied for clinical research.

I could have a table that lists these permutations above, but I'm concerned that this could confuse the data entry person, as the present version uses a multivalued field to record the sub-methods.

 

[plog]

Sounds like my previous suggestion about an additional table was pretty spot on. Deliveries will now have a LabourType field (None, Spontaneous, Induced) and then you need another table to capture all those other items:

LaborMethods
DeliveryID, Method
14, ARM
16, PG
16, ARM
17, Oxytocin
18, ARM
18, Oxytocin
23, ARM

From a form perspective, they select the labor type and then a sub-form with a drop down appears and they can select the relevant LaborMethods.

 

[pivottable]

Deliveries will now have a LabourType field (None, Spontaneous, Induced) and then you need another table to capture all those other items:

LaborMethods
DeliveryID, Method
14, ARM
16, PG
16, ARM
17, Oxytocin
18, ARM
18, Oxytocin
23, ARM

From a form perspective, they select the labor type and then a sub-form with a drop down appears and they can select the relevant LaborMethods.

Yes that sounds good!

Can I confirm that as each DeliveryID can have one or more LaborMethods, I would need to make this a Many to Many relationship via a junction table.

I guess I should post on the Forms forum to ask about subforms with drop downs to allow conditional selection (cascading combo boxes??) and multiple selection (list boxes?)

 

[plog]

I think a junction table might be over doing it. If you aren't storing other fields with the Method, you can just store the text value in LaborMethods.

 

[pivottable]

Isn't the relationship between tblDeliveries and tblLaborMethods many to many?

As in,

One delivery can have many labor methods; and

One labor method can apply to many deliveries.

In your example, I took it to mean that DeliveryID was the foreign key in tblLaborMethods, related to the DeliveryID primary key of tblDeliveries.

And Delivery No 16 had PG and ARM, and No 18 had ARM and Oxytocin

 

[plog]

Yes, that is the relationship, but an additional table for them might be unneccassary. If you have no other fields to go with their names there's no point in making a table for them. This is what I would call over normalizing and what I believe Rain was talking about.

 

[pivottable]

I agree that I have over normalised.

For the LabourType field, I planned to have a separate table with 3 options
None
Spontaneous
Induced

Instead of a separate table, should I just have a field, and enter data using a combo box in a form where Row Source type is Value List, and I type in the 3 options in Row Source?

If that is correct, I can reduce the number of tables by a few...

 

[RainLover]

Hey Guys,

Am I done here? Is there anything more I can advise on. Plog appears to have a good grasp on things.

If you need my help just ask. If I don't answer send a PM.

Remember there is a big time difference between us.

It is 6:00 PM here NOW.

 

[plog]

Yes a combo box in that manner for LabourType is the correct way to go.

You would need a LabourType table if there was more information associated with each type, but there's not, there's just the name. So, just use a drop down that goes into one field.

 

[RainLover]

That is hard coding. The only way to change it is to reprogram. A table can be updated..

MORE ADDED NEXT POST

 

[RainLover]

When you are finished the project you will then compile and give all the users their own separate front end.

If you have used this method of creating a Dropdown list, then you are locked in. That is you cannot change the values in the List. They are hard coded.

To change these you have to go to the master copy, make the change, compile and redistribute. If it were in a Table in the Back End the change/update would be so much easier.

I can't say that either is right or wrong all I can do is make you aware of a possible problem.

If you feel as though you have too many tables then you could add another level of naming. I assume you have adopted a Naming Convention by now. Not what you first showed us.

If you have Tables like tblColor or tblSize change these to tblLuColor and tblLuSize. The LU is for Look Up.

 

[pivottable]

Hi Rain,

Thanks for your invaluable advice. I understand the difference between hard coding a combo box value list and using a table for the lookup. I agree the table is much easier to make future changes. For fields like Gender, where the only 2 options will ever be M or F, I am happy to hard code that.

With regard to other fields that will be entered via combo box, is it OK to look up a standalone table, meaning that no relationship is defined between it and the main table? Using your suggested naming convention, those with prefix "tblLU" are not related to any other tables. They are just there to be looked up for the relevant form. I am wondering about this since every related table would require a separate subform to enter data. Or is my understanding flawed?

 

[Frothingslosh]

Rain's probably asleep right now, but in answer to your question, it's perfectly fine for lookup tables to be stand-alone tables.

I've not followed the thread closely enough to answer your last question completely, but it's far from uncommon for databases to make extensive use of subforms.