Database Normalization

Question

I need help in normalize a database. I am working on a database that is messy. I had many lists yes no lists and each list had a different Id for yes ,no  etc. I have combined all this lists in one universal list. i modified the data in columns to correspond to the new created list, but now i have a problem with lookup table. I would like to get rid of all the lookup tables, so i will be able to have a foreign key in all the tables related to the universal created list. i know how to do it manually, but there are way to many lists, so i am wondering if i can do this using vba code

Answer 1

No, You can remove the Lookup field designation the way Ken described. So its not necessary to delete the field. However, in your situation, since you have to update the data in the FKs, you have to use a separate field (i.e after you update all the 5s to 1, you can't update the 1s). So once you have the new field correctly populated its eaiser to just delete the old one.

Answer 2

The way I understand it, you have that shows a status (available" with ID 1 , "broken" with ID 2 , "sold" with ID 3 and "ordered" with ID 4). When developing the app, you created lookup fields on the table level and pointed them to different lists so that a status of available might have one value in one table and a different value in another table. Is that correct?

So what I would do is add a new Status field to your table (that is not a lookup field). then run Update queries to set the values properly. For example:

UPDATE table SET NewStatus = 1 WHERE OldStatus = 5;

Once you have NewStatus set correctly, remove the OldStatus field and rename NewStatus to what OldStatus was. You will then have to redo your relations. But this will fix your FKs and removing the OldStatus field removes the lookup field. You can follow this same procedure with any fields that were created incorrectly.

Answer 3

Let's say you have a dropdown Male/Female in several parts of your application. Clearly you should have ONE table tblGenders with two records in it.

However, that constitutes SAME meaning.

You seem to be talking about DIFFERENT meaning. That calls for SEPARATE tables. Maybe "available=5" is for a list of available products to ship, whereas Available=4 is for a list of available shipping routes. I'm making this up because you're not sharing a lot of information, but I hope you get the drift.

Answer 4

What i am trying to do is to get rid of the lookup fields at the table level. I have more than 10 lists that use the same values with different meanings. In one list "available" has an ID 5 as primary key, but in other list same "available" has an ID 4. Now I combined them in one list under one ID, so now I have "available" with ID 1 , "broken" with ID 2 , "sold" with ID 3 and "ordered" with ID 4. Now i want all my lookup fields on the table level to be removed, and all those table to get the foreign key from my new list

Answer 5

A universal list of lookup values is almost certainly a REALLY BAD IDEA.

Publish your Relationships window, and a description of what this database is supposed to do, and we may be able to offer alternatives grounded in relational database theory.