Need to Normalize

[maw230]

After posting my db in the Forms forum to get help with record updates, i was made aware that my db needs to be normalized. i am new to creating my own database tables and relationships, so i could use some direction.

my database is designed to store information on store Audits. a store can pass or fail their audit and depending on the result of that more information will need to be stored.

i think the best approach would be for me to post a copy of the original table that stored all of the records, and then post what i (with the help of this forum) have come up with for my tables and relationships. feel free to suggest an alternative..

 

[maw230]

a lot of the fields are Null and im wondering if that might be an issue. for example there are only 2 CheckNumbers out of 35 or so records..

 

[Galaxiom]

The relationships look OK.

However I would strongly recommend you remove the slash from all names. As a special character used in dates and division you would have to place the name in square brackets every time you type it.

Also although they are used by many developers, I am not a fan of underscores in names. They contribute nothing to the readability and are just extra keystrokes.

The meaning of PassFailID is still clear and easier to type than [Pass/Fail_ID]

Underscores are used in VBA sub names to separate control names from actions, and the naming of public procedures when called from another module. IMHO better to let VBA have them to itself.

 

[Galaxiom]

Nulls are not an issue. You just need to use an outer join in queries when you require all the records.

 

[maw230]

thanks for the advice on the naming.

as for being normalized, i must be a few steps off. when i try to add a new record to my subform within the main form i get the error "Cannot add records, join key of table xxxx not in recordset "

it may be a problem with my master and child links as im not too familiar on how to set those up.

 

[Galaxiom]

I have not come across that error before. Have you got all the fields that are involved in any relationships included in the recordset (recordsource query) of the form?

The join fields for a displayed recordet can be taken from any table included in the From section of the query. However it may be that you need to include the join fields from both of them in the recordset to be able to add new records. I really don't know.

Master and Child links are just a list of fields that identify the connection between the recordsets of the form and subform.

 

[maw230]

Thanks Galaxiom. apparently i needed to change a few joins around and basically just modify the recordset query for the subform and everything appears to be working... for the moment

 

[fleetman]

I too am working on a new data base. I have no formal training so there could be hugh gaps in my knowledge. I think the process of Normalizing the data is simply to use your data base, discovering missing data in varoius fields and populating the fields is the normalizing process.

Is there more to the process ?

 

[Galaxiom]

I think the process of Normalizing the data is simply to use your data base, discovering missing data in varoius fields and populating the fields is the normalizing process.

Is there more to the process ?

Much more. Normalization is more about structuring the information in tables. When it is done properly the data is compact, self-consistent, highly accessible, easily manipulated and the information capacity is expandable without changing the table structure.

Many new developers treat tables like spreadsheets and this mindset must be overcome. Spreadsheets keep all related data together. In a database related information about an entity will often be spread out across several tables. It is counter intuitive to humans but that is the way databases work best.

Search the forum for normalization. It is really important that you thoroughly understand this concept.

 

[fleetman]

Thanks for the follow-up...

Since the post, I have done a little more research. It seem like most of the process is thought through before the tables are built and linked. The process I was thinking about was more like finding a missing piece while using a " lookup table" to a new record. Something like Make or model. The lookup field linked to the Make table has Ford, GMC but Mack is a new make. Maybe your first Mack truck.

Continuing along the missing data theme would be the purchase order used to buy the unit.

Another thought is while using the database ideas about how to reduce key strokes. The thought I am working on now is take data from the preventive maintenance work-order and update pm data fields in the unit table.

In any event, when finished I expect a Fleet Management Database equal to or better than most Commercially availible programs. One reason it will be better is that it will contain real data for parts and usage, vendors and purchases, units and actual repair histories and fuel use and inventory records, MSDS sheets and google map interface for the entire county to find broken equipment in unfamilier areas of the county.

Want to help