Need Advice (1 Viewer)

[Bee]

Hi,

My client wants me to make fields from different tables on the same form which he wants to use for input. This has made it very difficult for me as my queries have to involve a lot of outer joins and in some cases full joins.

Any suggestion please?

 

[MStef]

Hello Bee!

It seems to me theat this mdb is not normalized well.

 

[Bee]

It seems to me theat this mdb is not normalized well.

What do you mean? Do you want me to post my relationships to have a look?

 

[Uncle Gizmo]

Do you want me to post my relationships to have a look?

It might be an idea!

Cheers Tony

 

[Bee]

Relationships image

Thank you.
B

 

[Bee]

I wonder if anyone can help me with this issue on Normalization?

Basically, I have tblEmployee, tblHouse, tblPhase.

tblEmployee: stores employees' details and what kind of job they do. e.g Plumer.
tblHouse: stores info about houses.
tblPhase: stores info about phase (Each Phase have many houses).

Employees install services in a phase or house.
A house can either be part of a phase or be a stand alone house. So, services can be done to both phase and house. If a house is part of a phase, services are done to a phase only, but not to each house. if the house is stand alone, it gets services independently. Stand alone houses are not part of any phase.

So, I created a table called tblServices that stores the type of job done and it has a foreign key of Employee, Phase and House. The problem is that when i fill each record of this table, i will always have one Null foreign key and that will be either house or phase because each job is done to only one of them.

Is that Ok? or can It create big problems later on?

 

[WindSailor]

The problem is that when i fill each record of this table, i will always have one Null foreign key and that will be either house or phase because each job is done to only one of them.

Bee,
That was a violation of First Normal Form.

First Normal Form
For a table to be in First Normal Form (1NF), each row must be identified, all columns in the table must contain atomic values, and each field must be unique.

Or

First normal form (1NF) lays the groundwork for an organized database design:
* Ensure that each table has a primary key: an attribute or combination of attributes whose values are guaranteed to be different for every record of the table.
* Eliminate repeating groups (categories of data which would seem to be required a different number of times on different records) by defining key and non-key attributes appropriately.
* Atomicity: Each attribute must contain a single value, not a set of values.

Second normal form (2NF) requires that data stored in a table with a composite primary key must not be dependent on only part of the table's primary key:

* The database must meet all the requirements of the first normal form.
* Data which is redundantly duplicated across multiple rows of a table is moved out to a separate table.

Third normal form (3NF) requires that data stored in a table be dependent only on the primary key, and not on any other field in the table.

* The database must meet all the requirements of the second normal form.
* Any field which is dependent not only on the primary key but also on another field is moved out to a separate table.

Go back and take a look at the Normalization links or do a search here.
There are plenty of examples and tutorials.

-------------Edit---------
You are going to have to work through the normal form examples with your template.
Set your normal form rules on a separate piece of paper as a guideline and then simply start with 1st normal form and move on (yes, I would work the whole thing out on paper several times...).
It is very hard to post all of the components/concept of your database in a forum (I originally thought every field was required for each record from your earlier post...), that is the reason why I usually just post links and let the person work through the multiple examples and grasp the concept from there...


http://www.access-programmers.co.uk/forums/showthread.php?t=100211

There are others...

 

[Bee]

Bee,
That was a violation of First Normal Form.

Hi WindSailer,

I appreciated your answer. I quite understand the concept of normalization and still could not see what was wrong with my database. I wonder if you could tell me what was the violation. If you could give an example, it will be great.

Have you taken a look at my relationships picture attached?

 

[WindSailor]

It was just an observation of what you wrote... and no, originally I didn't look at the example. Looking at it now I can't immediately see the issue.

I was going on your statement; when you created a new record in tblServices, you always have a null value for a foreign key.

I have always thought that a null value was not accepted in first normal form, that each column must be unique and have a value.

 

[Bee]

It was just an observation of what you wrote... and no, originally I didn't look at the example. Looking at it now I can't immediately see the issue.

I was going on your statement; when you created a new record in tblServices, you always have a null value for a foreign key.

I have always thought that a null value was not accepted in first normal form, that each column must be unique and have a value.

I will only have a null value in table tblServices once in each row, but I will have a value for the other two foreign keys and the reason for that is because a service is given to either house or phase. It can't not be to both and it cannot be to none.

And that's where my concern is. I thought of creating two seperate services tables e.g. tblHouseServices and tblPhaseServices. But because both of them will receive exactly the same services, I did not see much of a point.

If you think there is a flaw, let me know please.

Thanks,
B

 

[WindSailor]

Bee,

I was wrong about the null value issue in a big way -oh brother-... as soon as I left for work, I realized the dastardly deed was already done... no excuses here. Humility, at least there is a name for it...

In my little part of designing, I normalize to a higher normal form to organize or list all of my attributes and many to many tables and then de-normalize to shrink the number of tables back. But on my many to many tables I have the FK's a required field. It really is kind of a strict sequence. In reality I think it isn't as open as yours, because you can search for nulls.

Let me talk about it here locally and I will get back to you.

 

[Bee]

Bee,

I was wrong about the null value issue in a big way -oh brother-... as soon as I left for work, I realized the dastardly deed was already done... no excuses here. Humility, at least there is a name for it...

In my little part of designing, I normalize to a higher normal form to organize or list all of my attributes and many to many tables and then de-normalize to shrink the number of tables back. But on my many to many tables I have the FK's a required field. It really is kind of a strict sequence. In reality I think it isn't as open as yours, because you can search for nulls.

Let me talk about it here locally and I will get back to you.

WindSailer,

That's fine, when you first said that I was getting paranoid here.

Hope you let me know once you talked about it.

B

 

[liddlem]

Hi Bee
Would your problem be solved if you had a series of "dummy" houses to accomodate each Phase?

I.E
House ID = 42 (The answer to the whole universe)
House No = Phase No
Plot No = Phase No
Phase = Phase

When looking for individual houses, point to a query that excludes records where plot no = Phase No.

 

[Bee]

Hi Bee
Would your problem be solved if you had a series of "dummy" houses to accomodate each Phase?

I.E
House ID = 42 (The answer to the whole universe)
House No = Phase No
Plot No = Phase No
Phase = Phase

When looking for individual houses, point to a query that excludes records where plot no = Phase No.

Hi liddlem,

I don't quite understand your point. Plot No and House ID are in the same same table. House ID is a PK, but Plot No is only a normal field.

Can you specify please?

Regards,
B

 

[WindSailor]

Bee,

The 'local' guys here are off on other projects and not available.

In regards to having a Null value for a foreign key:

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=3&Number=1258379&page=0&view=collapsed&sb=5&o=&fpart=1

 

[The_Doc_Man]

Employees install services in a phase or house.
A house can either be part of a phase or be a stand alone house. So, services can be done to both phase and house. If a house is part of a phase, services are done to a phase only, but not to each house. if the house is stand alone, it gets services independently. Stand alone houses are not part of any phase.

In a word, No. This is a basic set-theory issue. You are violating one of the completeness theorems, I forget which one.

To do this "right" ALL HOUSES are in a phase - even if it is a one-phase house. I.e. to normalize it properly, you NEVER EVER have a house that isn't in a phase. Then you get rid of ALL of your outer joins.

Now, if you want to have a phase that says "this phase contains all stand-alone houses" - that is OK, it is still a valid one-to-many relationship just like the houses that are in your more "normal" phase. But the more important part is that the linkages are never null.

It also becomes easier to manage this situation because now you have a "special" phase that you can treat specially, perhaps by having a set of queries that include WHERE .... AND ( Phase <> "SpecialCase" ) ... to handle normal phases and a second set of queries where you replace <> with =

 

[Bee]

In a word, No. This is a basic set-theory issue. You are violating one of the completeness theorems, I forget which one.

To do this "right" ALL HOUSES are in a phase - even if it is a one-phase house. I.e. to normalize it properly, you NEVER EVER have a house that isn't in a phase. Then you get rid of ALL of your outer joins.

Now, if you want to have a phase that says "this phase contains all stand-alone houses" - that is OK, it is still a valid one-to-many relationship just like the houses that are in your more "normal" phase. But the more important part is that the linkages are never null.

It also becomes easier to manage this situation because now you have a "special" phase that you can treat specially, perhaps by having a set of queries that include WHERE .... AND ( Phase <> "SpecialCase" ) ... to handle normal phases and a second set of queries where you replace <> with =

So, you mean I should create a dummy phase in the tblPhase to cater for all independant houses. If I do that, that means that dummy phase should never be deleted or ulterred.

Do you mean I should never have a null value for FK and should create other dummy records for my other null FK?

Is that just because Access can't handle many outer/full joins? In my DB course, it was very normal to use nulls for FK!

Regards,
B

 

[Bee]

Bee,

The 'local' guys here are off on other projects and not available.

In regards to having a Null value for a foreign key:

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=3&Number=1258379&page=0&view=collapsed&sb=5&o=&fpart=1

I have just created an account!
Thanks,
B

 

[Bee]

In a word, No. This is a basic set-theory issue. You are violating one of the completeness theorems, I forget which one.

To do this "right" ALL HOUSES are in a phase - even if it is a one-phase house. I.e. to normalize it properly, you NEVER EVER have a house that isn't in a phase. Then you get rid of ALL of your outer joins.

Now, if you want to have a phase that says "this phase contains all stand-alone houses" - that is OK, it is still a valid one-to-many relationship just like the houses that are in your more "normal" phase. But the more important part is that the linkages are never null.

It also becomes easier to manage this situation because now you have a "special" phase that you can treat specially, perhaps by having a set of queries that include WHERE .... AND ( Phase <> "SpecialCase" ) ... to handle normal phases and a second set of queries where you replace <> with =

Houses that are part of phases can have services independent of the phase they relate to. That makes them independent, but we should still be able to know which phase they relate.

So, If we create a dummy phase to accommodate for all stand alone houses; a house that used to relate to phase number 7, will start to relate to the dummy phase.

 

[The_Doc_Man]

Then you have a basic concept-level design issue that is a LOT more complex than you really described. And I stand by my statement that you do better by having a dummy phase. However, from your last comment, it sounds like time/date becomes an element of identifying phases. Now you are REALLY getting into normalization issues.

It sounds to me like you have phases and houses, but membership in a phase is time-variant. Which means you now need a junction table that includes time. (Otherwise you have elements in the table not dependent on all of the table's keys.)

House: HouseID, address info, etc.
Phase: PhaseID, phase info
HP_members: HouseID, PhaseID, PhaseStart (date/time), PhaseEnd (date/time)

Normalization isn't just a "good" idea - it is based on a set of proofs related to the concept of "computability" - i.e. the ability to compute a result for a given question. With a properly normalized table you can quickly determine whether the information you want is obtainable. If the table is not normalized, you have the very good chance of having inaccessible data because there is no well-defined set-theory-based path from point A to point B.

You see, the reason I am taking a tough line on this is that you complained about all sorts of outer joins (thereby introducing nulls in JOIN queries) and having multiple conditions because of houses in various - or NO - phases.

You were looking for a neat, clean way to eliminate the complexity. The EASIEST way is to eliminate the outer joins and have a dummy phase so that you have no excluded houses. If ALL houses are members of a phase, no matter what that phase happens to be, then you have no more outer joins. You have no more nulls. You have eliminated the nastiest aspects of the special cases that were vexing you.

I'll finish by saying this: You will NEVER get Access to do on the computer something you yourself could not have done on paper. And I'm not talking about the tedious nature of doing it on paper. If you can't do it on paper, how will you ever tell Access how to do it? So this is beginning to smell like a case of poor workflow design or poor understanding of the workflow. I can't tell which from my end. But it is a design-level issue that must addressed very thoroughly before you have any hope of making progress.

Bee, please take this as an attempt to be helpful. If it comes across as a bit of a lecture, just remember my title is not medical. I used to be a college-level instructor. And you can take the Doc out of college instructing, but you can't take the college instructing out of the Doc.