Normalisation

[Pharcyde]

Following on from a post yesterday "Strange Things Happen" to which Mile-o-Phile gave me a link to a normalisation thread he was involved in.

Read the thread, understood the concept, so then had a look at my tables. They all seem to work fine, so that left me

lol. From what I can see they cannot be broken down any further. But I know this can't be true otherwise Mile-O wouldnt have given me the link...

Could anyone please have a look at my attachment and tell me where the problem lays?

Thanks loads, Lee

 

[Mile-O]

Here's my normalised interpretation of what you want. A further table, perhaps, could be used to archive assessments once they have expired.

 

[Mile-O]

And here's the database so that you can look deeper into the table structure:

 

[Pharcyde]

as always, you have been very helpful. Thanks very much,
Lee

 

[Mile-O]

Don't you even want to know why I put a DateExpired field in the tables? Why, especially, this could be important for tblAssessments?

 

[Pharcyde]

hmmm. hadn't paid much attention to it. It expires in 2200 so won't be around

But seriously, yes please, I would!

I also need to keep the table that holds all the set assessment dates - only just noticed it had gone. Asides form that its perfect...

 

[Mile-O]

Having a DateExpired field with a date so far in the future will indicate that the records are active. Rather than delete records as you decide they are no longer needed you can instead use an UPDATE query to change the DateExpired to the current date so that you can do two things: i) differentiate between active and current case; and ii) know exactly when a case became inactive.

With the Assessments you introduce price which - no doubt - can change over the course of time. When totalling, having the assessment once and totalling would give incorrect answers. Updating an assessment's price and removing the old record for it will allow historical calculations to be correct.

As for set assessment dates, the tblStudentsToAssessments table stores the date of an assessment.

 

[Pharcyde]

aye. that all makes sense. but i'm sure that i definitely need the assessment dates in a separate table.

One thing that confuses me though, is that in the relationships window it keeps putting tblResults_1 in. Why does it do that!?

 

[Mile-O]

aye. that all makes sense. but i'm sure that i definitely need the assessment dates in a separate table.

Why? That would denormalise the structure. With what I've given you you can say "give me all the assessments taken on such and such a date" and it's just a case of querying that one column in the table tblStudentsToAssessments.

What you are suggesting is the duplication of data which will cause problems. Get a date wrong and you have to change both records, etc.

Believe me; you don't need another field for assessment dates if you already have one. There is not justification for an assessment date table as it is dependant upon the assessment and the student.

One thing that confuses me though, is that in the relationships window it keeps putting tblResults_1 in. Why does it do that!?

Because you are putting the table in for a second time i.e. creating a second instance of the table. If you can't see the original table try looking at Show All in the menu.

 

[Pharcyde]

But not on the .zip you gave me. All i did was put data in the tables. Maybe I'll just take it as given, because it doesnt seem to affect the db really...

 

[Pharcyde]

ok. its not that I'm not listening to you, because you clearly know a lot more than I do. I guess I'm just not understanding it....

Maybe If i try and explain(for myself as much as you! lol) ...There are a set of dates where accreditation/submission/results take place.

~~~~~~~~~~~~~~~~~~
i.e so we have:

Exam-Submission-15/12/2002
Exam-Result-23/01/2003
report-Submission-14/01/2003
report-Result-23/03/2003

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So I'm thinking I need a table holds the type of assessment, whether it be res/sub/acc, and its date...

The Result dates are also accreditation dates. Do you follow, or am I talking a complete load of Gumbo here??

 

[Mile-O]

Okay, how about Event Types and Events then?

 

[Pharcyde]

Just tried it. Perfect, thats exactly what I was looking for! I'll leave u alone now mile-O. Cheers for all the help on this, much appreciated!!

 

[Mile-O]

No probs!