Trouble with Relationship set up (redundant data)

[JD2775]

Hi all,

I am trying to create a Greenhouse Maintenance database that will span 4 different location sites. I am stuck at a point where it doesnt appear my database is "normalized" at all and I have repetitive data. I am going to explain how the main table (tbl_Maintenance) works so you can see where I want to go with this.

Date (current date)

Person (conditional value list dependent on Location chosen)

Location (CA, AZ, OR,...)

Greenhouse (specific greenhouse number/ value list dependent on Location chosen)

Issue (generic value list across all sites)

Description (free text)

Currently I have this broken down into 5 tables

tbl_Maintenance (with fields shown above, this is the data entry table)

tbl_Location

Location (linked to tbl_Maitenance)

AutoID

tbl_Greenhouse

GreenhouseID

Location (linked to location in tbl_Location)

tbl_Crew

Person

Location (linked to location in tbl_Greenhouse

tbl_Issues

Issue (linked to issue in tbl_Maintenance)

AutoID

[comment]

The only redundant data I see is the Location field in the Maintenance table. Once you select the GreenhouseID, the location is given. However, if you want to select the greenhouse from a value list of greenhouses in the chosen location, you need to choose a location first - and the choice needs a field. Depending on your UI, perhaps you could use a global field for this, thus removing the redundancy - but I don't think it's critical.

[JD2775]

The only redundant data I see is the Location field in the Maintenance table. Once you select the GreenhouseID, the location is given. However, if you want to select the greenhouse from a value list of greenhouses in the chosen location, you need to choose a location first - and the choice needs a field. Depending on your UI, perhaps you could use a global field for this, thus removing the redundancy - but I don't think it's critical.

Thanks Comment (you have helped me before but I have forgotten your name...)

Anyway, I need it to work the opposite as you have described. The Location needs to be chosen first (CA, AZ...) so that way the GreenhouseID value list can populate. There are many GreenhouseID designations to one location. Does that make sense? If I did it the way you are describing I would have a value list of 150+ GreenhouseID's to choose from, in order to have my location a given.

Sorry, I wasnt clear on the "one to many" relationship in the beginning.

Also, I didnt realize this but my edits to my original post never saved There is additional information in bold and an additonal table if that changes anything

[comment]

I need it to work the opposite as you have described.

I thought I have described it exactly the same way as you did. Since you want to choose the location first, you must have a redundant Location field in the Maintenance table.

[JD2775]

I thought I have described it exactly the same way as you did. Since you want to choose the location first, you must have a redundant Location field in the Maintenance table.

You are correct, sorry, I mis-read your reply (focusing on the first part of it only). So it's not a problem having location data in tbl_Location, tbl_Greenhouse and tbl_Crew?

Thanks again

[comment]

It is a problem as it is redundant, hence there can be a conflict between the location and the chosen greenhouse. However, there's not much you can do about it - except maybe set the GreenhouseID field to auto-clear itself when the location is mismatched, and validate it for not empty (or just validate against a conflict).

See also:

http://fmforums.com/forum/showpost.php?post/195641/

OTOH, since you are not using the chosen location for anything else other than selecting the greenhouse, you could just shrug and move on.

Edited January 27, 2011 by comment

[JD2775]

It is a problem as it is redundant, hence there can be a conflict between the location and the chosen greenhouse. However, there's not much you can do about it - except maybe set the GreenhouseID field to auto-clear itself when the location is mismatched, and validate it for not empty (or just validate against a conflict).

See also:

http://fmforums.com/forum/showpost.php?post/195641/

OTOH, since you are not using the chosen location for anything else other than selecting the greenhouse, you could just shrug and move on.

You are right I think. Given the fact that I need to use conditional value lists these tables need to be seperate and linked by Location most likely (otherwise I could have combined some of them into one table). If you think it's not a big deal I will go with it. Also, I will check out that other solution you provided with the link, to see if that could be applied here as well.

Thanks