Relational Databases - An Example - normalising

This page follows on from Setting up a form page.

At the moment, if we want to enter information from a food packet with two columns (say per serving and per 100g.) then we need to enter two records which involves duplicating some information.

So, how do we avoid this duplication? We want to add an other table as follows:

tables

Notes:

  1. Instead of using ID in product table it would be better to use barcode as the unique key. Some existing entries don't have a barcode, although all new entries will have a unique barcode. Therefore we need to replace any empty barcode values with a unique but not not valid pseudo-barcode value.
  2. per will be one of: 'per serving','per pack','per half pack', 'per 100g','per portion', etc.
  3. if 2 is 'per portion' then weight is the weight of a portion.

Now we can have upto 2 'byWeightQuantities' entries for each product entry, but how do we convert the information that we already have in the database?

I guess we could convert the existing table back to a CSV again (this time including ID field), make a copy of this file, then edit the two files so that one only contains the 'product' information and the other only contains the 'byWeightQuantities' information.

But how do we keep the two tables linked?

The only way I could think of doing this was to copy the table back into the Calc spreadsheet program and then split the table and copy it back, this is just the way I found of doing this it might be possible to do this completely in base?

Anyway I copied the table

copy table

and pasted it into calc:

paste to calc

I then put values into the empty 'barcode' fields so that it can later be used as a key index. I just used 't' followed by the ID number, where the product is the same, I used the same 't' value:

make barcode unique

I then saved two copies of this spreadsheet, one for 'byWeightQuantities' and one for 'product', we can then edit the two tables separately.

save as ods

Modifying product table

In the 'product' version I deleted the ID column:

product  1

and also deleted the energy to sodium columns:

product 2

Then, where there are 2 rows for a product, I deleted the second entry sot that there is only one entry per product.

product 3

Also, in the description field, I removed any information that applies to the 'byWeightQuantities' table such 'per 100g'. This leaves the product information:

product 4

Modifying byWeightQuantity table

In the byWeightQuantity table delete 'ID' and 'type' columns:

by weight 1

and delete the 'hydrodised' column:

by weight 2

We then change the 'description' column to 'per' column and delete all text apart from the 'per' quantities.

by weight 3

Copy and paste back into Base

We can now select all the cells in Calc and copy+paste back into Base. We separately copy back the byWeightQuantities and product tables in a similar way we did for importing. When copying back the product table don't select 'Create Primary Key' as we want to use barcode as the primary key.

two tables

The final step is setting barcode to be the primary key in the product table.

Right click on the left hand cell next to barcode in product table design, set primary key:

primary key 1

When I tried to save this I got the following error message:

primary key 2

pressing more gives:

primary key 3

This means that some of the entries in barcode are duplicated, I therefore had to go back to the speadsheet, remove the duplication, and re-import it.

Next stage - go on to: form for many linked tables

Other sections are:

Data:


metadata block
see also:

 

 

Correspondence about this page

Book Shop - Further reading.

Where I can, I have put links to Amazon for books that are relevant to the subject, click on the appropriate country flag to get more details of the book or to buy it from them.

 

Commercial Software Shop

Where I can, I have put links to Amazon for commercial software, not directly related to this site, but related to the subject being discussed, click on the appropriate country flag to get more details of the software or to buy it from them.

 

This site may have errors. Don't use for critical systems.

Copyright (c) 1998-2017 Martin John Baker - All rights reserved - privacy policy.