Normalization Table Levels

[RockKandee]

I need some advice with the normalization of my database.

I am using Access 2013 with Windows 8

I have tables that are different levels. The bottom level table is an in progress table. It has many fields that only hold data for current projects. Once the project is completed, a history table is appended with the appropriate data from other tables and the in progress record is deleted. I have no need to store the in progress data.

I have different project types where I need an in progress table. The in progress fields share a couple of fields but not many.

For example - One project type may need 30 fields and another project type may need 4 of those fields, while another may need 4 fields in common plus 10 additional fields.

My question is:

Should I use one huge in progress table or should I use a separate in progress table for each project?

I am not sure which would be more normalized. Since the in progress table will not have very many records at once, I am not sure if it is better to expand the number of fields, even if some are not needed in every case, or if it is better to have several smaller tables.

Thanks in advance for taking the time to read.

[zmbd]

Have you read thru this: > Database Normalization and Table Structures. ?

[RockKandee]

Yes I have read this as well as other articles about normalization. My question stands. I am unclear how to handle the in progress table(s). Thanks

[zmbd]

After a few hundred posts my mind goes a little soft (^_^)

Kind of hard to follow what you have there... this is what I'm thinking:

tbl_project
[project_pk]autonumber
[project_name]text(50)
[project_.... directly related fields no-data]

tbl_progress
[progress_pk]autonumber
[progress_fk_project]numeric(long) 1:m tbl_project
[progress_parameter]
[progress_data]

so, say you have Project = Train ZMBD how to dive
tbl_project
[project_pk]=100
[project_name]="Train ZMBD how to dive"
[project_certificationissued]=#01/01/2014# (I wish (-_^) )

tbl_progress
---record 780
[progress_pk]=780
[progress_fk_project]=100
[progress_parameter]=facemaskfitting(now this might be a related table)
[progress_data]=pass
---record 781
[progress_pk]=781
[progress_fk_project]=100
[progress_parameter]=divetimecalc
[progress_data]=pass
etc....

Now your table progress can have as many records as you need... say 30 steps for me and only 8 to recertify ADezii

Once you're done, DELETE query on [progress_fk_project]=100

[NeoPa]

Having a separate table for historical stuff is often a bad approach even. A small amount of info can differentiate the project data in progress and that which is historical. The design of the data doesn't change vastly at that point, so why is another table necessary. When someone wants to enquire of data across live and historical projects you are stuck with non-updatable UNION queries. This is something normalisation does for you if you let it.

It's hard, without knowing your project in as much details as you do, to know if separate tables may be of any benefit when different types require different information. In general though, the more you can fit together into a single table, the more benefit you can get from the RDBMS (in this case Access) being able to manipulate the data for you.

This is not a definitive answer, as I say I don't know your database as well as I would need to to answer reliably, but it's a fairly confident pointer to avoid creating more tables unless called upon to by the concepts of normalisation.

I hope you find that helpful Kandee.

[RockKandee]

I get most of the explanations above, I think ;)

Sounds like both of you agree all info should be in one table.

It would make it easier to keep it all in one table. I can't think of any benefit in using more than one table other than my human brain feels like it should be separated.

I just wanted to make sure that using 1 table still makes it normalized. Sounds like it does, if I understand the above correctly.

Thanks very much zmbd (rest that brain and learn to SCUBA!)

So I should merge my History table in with my In Progress table and let my Forms/Reports/Queries sort it out, correct? I see how that would make life much easier, I was just fighting with the clean up bug. Don't need it = make it go away - LOL

Thanks NeoPa - kisses

[NeoPa]

It sounds like you get what was being said Kandee. Less is more.