Splitting one table into 3 related tables

[pivottable]

Hi all,

I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now, and any suggestions would be gratefully received!

This is a database of pregnancies and deliveries.

The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.

Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.

This is because each mother can deliver more than once, and each delivery can have more than one baby.

I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.

The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.

I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.

Thanks in advance!

 

[Uncle Gizmo]

Have a read here. http://www.access-programmers.co.uk/forums/showthread.php?p=671226

 

[plog]

I like the concept, but there are a few issues I see:

Mother.NRIC - is this unique to the mother? If so, remove the ID from the Mother table and just use NRIC as its primary key. It seems the foreign key in Delivery (Mother_NRIC) is already expecting it but you aren't linking back to it, you are linking to the ID field in Mother.

Birth_Order - this is unnecessary because you are recording Delivery_Time. If you know the time of a specific baby, you can determine what order it came out. Birth_Order is redundant and shouldn't be stored.

Delivery.Delivery_Order - this is similar to why Birth_Order isn't necessary. A woman is only going to deliver once per date right? It's not like a baby is born, they take her to recovery and 12 hours later they wheel her back in to get another one out. Delivery_Order isn't necessary because it can be determined with the data you already have.

Baby.NRIC - does a baby get an NRIC? If so, I would use that as the primary key in the Baby table instead of an autonumber primary key.

As for moving your data from one set of tables to another, it really depends on what system you have currently. You may have to add additional fields to these new tables to temporarily accomodate any existing relationships you have among your data. Then you would import that data into your new tables, use the old relationships to establish what data is connected and then run UPDATE queries to find and populate the new tables with the correct foreign keys.

 

[Uncle Gizmo]

Mother.NRIC - is this unique to the mother? If so, remove the ID from the Mother table and just use NRIC as its primary key.

I would keep, and stick to using the ID myself, the problem with numbers that are supposed to be unique is there is always the possibility that they are not. There is an example of using someones National Insurance number, supposed to be unique, but wasn't. (Possibly an urban myth)

 

[pivottable]

Mother.NRIC - is this unique to the mother? If so, remove the ID from the Mother table and just use NRIC as its primary key. It seems the foreign key in Delivery (Mother_NRIC) is already expecting it but you aren't linking back to it, you are linking to the ID field in Mother.

I would keep, and stick to using the ID myself, the problem with numbers that are supposed to be unique is there is always the possibility that they are not. There is an example of using someones National Insurance number, supposed to be unique, but wasn't. (Possibly an urban myth)

Mother.NRIC: this should be unique, but I also understand Uncle Gizmo's point.

Birth_Order - this is unnecessary because you are recording Delivery_Time. If you know the time of a specific baby, you can determine what order it came out. Birth_Order is redundant and shouldn't be stored.

Delivery.Delivery_Order - this is similar to why Birth_Order isn't necessary. A woman is only going to deliver once per date right? It's not like a baby is born, they take her to recovery and 12 hours later they wheel her back in to get another one out. Delivery_Order isn't necessary because it can be determined with the data you already have.

Baby.NRIC - does a baby get an NRIC? If so, I would use that as the primary key in the Baby table instead of an autonumber primary key.

As for moving your data from one set of tables to another, it really depends on what system you have currently. You may have to add additional fields to these new tables to temporarily accomodate any existing relationships you have among your data. Then you would import that data into your new tables, use the old relationships to establish what data is connected and then run UPDATE queries to find and populate the new tables with the correct foreign keys.

Birth_Order and Delivery_Order: Thanks. Would it be possible in multiple births, for it to display "Twin 1 of 2" or "Triplet"... depending on how many births are recorded for a particular delivery?

Baby.NRIC: These should be unique, however, would not be created if a baby is stillborn. Stillbirths also need to be recorded in the database, so again, would probably use the AutoNumber ID.

Migration: presently all data is stored in one table, so there aren't any relationships. Could you elaborate on how you use an UPDATE query to find and populate new tables with correct foreign keys?

 

[plog]

Yes, its possible for you to spit out "Twin 1 of 2", "Triplet 2 of 3", but the pedant in me can't but help pointing out that the "Twin" part implies the "of 2" part.

For migration, can you post your current structure and some sample data?

 

[pivottable]

Yes, its possible for you to spit out "Twin 1 of 2", "Triplet 2 of 3", but the pedant in me can't but help pointing out that the "Twin" part implies the "of 2" part.

For migration, can you post your current structure and some sample data?

That's brilliant! "Twin 1", "Triplet 3" etc is so much more concise and elegant than my redundant "of X" part!

Excuse my ignorance, should I post a zipped .accdb file with structure and sample data as an attached file?

 

[plog]

Actually, on this system I have trouble opening .accdb files. Can you convert it to a .mdb then zip it and attach it?

 

[pivottable]

plog: thank you so so much!

 

[plog]

First, here's some assumptions about your new tables: Each one has an autonumber primary key named ID which will be a foreign key in the table it relates to. Mother.ID -> Delivery.Mother_ID, Delivery.ID -> Baby.Delivery_ID

Here's how to go about moving your data:

1. Create a new blank database and paste your new tables into it. Then paste in your old data table.

2. Add a numeric 'oldID' field to every new table, this will hold the ID field value from your old table. Also add a numeric 'newID' field which will hold the newID to use. Thise means you will have an oldID field, newID and an ID field on each table.

3. Run an APPEND query to move all the relevant data from your old table to the new Baby table. Be sure to include the ID field which will go into the oldID field. Your new foreign key field (Delivery_ID) won't be populated by this query.

4. Do the same for the Delivery and Mother tables. You will be appending dupes to these tables, but that's ok, we will take care of those in a minute. As long as you populate the oldID field we will know how to get rid of them. The foreign key field in Delivery (Mother_ID) won't be populated by this query.

5. Create a new aggregate query (use the Summation/Sigma symbol in the ribbon) on the Mother table. Bring down NRIC, Mother_Name and Date of Birth fields and the ID field. Underneath each it should say 'Group By' change the one underneath ID to say 'Min'. Save that query as 'Unique_Mothers'. You will use this query to eliminate all the duplicate mothers in your old data.

6. Create a new UPDATE query using the Unique_Mothers query and the Mother table. Link those two data sources by NRIC, Mother_Name and Date of Birth. Bring down the newID field from the Mother table and in the 'Update To' area have it update to the MinOfID field from the Unique_Mothers query.

7. Create an UPDATE query based on Delivery and Mother. Link the two tables by the oldID field. Bring down Mother_ID from Delivery and set the 'Update To' area to the newID value from the Mother table. This establishes the real link that you planned to have between Mother and Delivery tables.

Hopefully you understand what happened up to here, if not let me know. Without going into as great of detail, you perfrom the same process between Delivery and Baby that you did between Mother and Delivery---create a Unique_Deliveries query, link that query and Delivery to update the newID field in Delivery, then use that newID value as the Delivery_ID value in the Baby table.

After that you can delete all the records in Mother where newID doesn't equal ID as well as those in Delivery where newID doesn't equal ID. Those are the dupes and their ID values shouldn't appear as foreign keys in the other tables. Of course, I'd run some queries to make sure this is true.
Once you have done that you can delete all the newId, oldID fields from all tables.

 

[pivottable]

I've got as far as step 6. Unfortunately when I try to run the UPDATE query, I get an "Operation must use an updateable query" error.
Please refer to the attached screengrab. I've attached the sample database too.

When trying to figure out what this error is, I came across this page:

support.microsoft.com/kb/175168

Not sure if any of it is relevant. I have full write permissions for the folder in which the .mdb file is located.

 

[RainLover]

I am going to guess that the Query Unique_Mothers is itself not updateable.

Open the Query and see if you can add a record. If not then this is where your trouble lies.

As far as the earlier discussion about using AutoNumber. It is correct to use either a Natural Key like NRIC or a Surrogate like AutoNumber.

It is possible to make a mistake with a Natural but you can't do the same with a Surrogate.

I always use AutoNumber.

 

[pivottable]

I am going to guess that the Query Unique_Mothers is itself not updateable.

Open the Query and see if you can add a record. If not then this is where your trouble lies.

If I understand correctly, I am trying to update the Mothers table with a field from the Unique_Mothers query, not the other way around.

Would it still require the Unique_Mothers query to be updateable?

 

[RainLover]

Most definitely YES.

It sounds like I picked the fault. Is that right.

 

[plog]

Turn Uniqu_Mothers into a MAKE TABLE query and create a table called 'tmp_Unique_Mothers'. Then use that to update Mothers.

 

[RainLover]

Another design fault which must be fixed before proceeding.

Your table design should not have any Lookup fields.

There is a great article on "The Evil of Look Ups" I suggest you do a Google and read this. As a special note, this refers to Tables as the article says.

The use of Look ups in Forms is not only acceptable but it is good practice. Just kill the combo boxes / lookups in Tables before doing anything else.

 

[RainLover]

I really don't like the naming system you have employed.

There has been many different ideas put forth on the subject. The important thing that you should be doing is using one of these. A bunch of reasonable rules is better than none at all.

Try this one http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[RainLover]

Sorry about this but I keep coming up with things I believe you need to know. You are at a stage of your learning where things are just starting to fall into place hence you have a lot of things buzzing around in your mind. Let's make sure you get these right now instead of going back later and relearning.

The Primary Key and the Foreign Key should never be seen by the end user.

You will use them a lot in your Queries and Tables. These keys are for correctly identifying a record. The Primary is unique but the Foreign can be a many as required. Thousands if required.

But as I said " The Primary Key and the Foreign Key should never be seen by the end user."

 

[pivottable]

Sorry about this but I keep coming up with things I believe you need to know.

Thank you for your excellent advice. I'm feeling my way by trial and error, and your tips will be invaluable for improving my database design!

 

[pivottable]

Wow.

Having read several tutorials on normalisation, from one table, to what I thought would be three, has ended up with 24!

I'm now dreading the next step: query, form and report redesign! But I'm confident that with the help of this great community that if I stumble, I'll be able to get help!

Thanks to everyone who has contributed their suggestions.