Jump to content

5 tables and one headache


MarcoB

This topic is 6106 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have 3 tables (all of them are one-to-many) that are combined to make other 2 tables:

OWNER

ownerID.pk

FARM

farmID.pk

ownerID.fk

BLOCK

blockID.pk

farmID.fk

block_number

OBS: blockID.pk is a serial number for all the the blocks from all the farms in the table BLOCK. block_number.pk is a name we give to the block and is used in the farm, we often use to number it is sequence. One farm can have from 2 to 200 blocks.

ex:

[color:red]blockID.pk farmID.fk block_number

1 1 1

2 1 2

3 1 3

4 2 1

5 2 2

6 3 1

7 3 2

The tables have more fields, these are the basics fields you need to understand my problem.

The relations between these 3 tables are fine and working correct.

Now I have 2 more tables, and my problem starts here. Every six months we have to make an inspection in blocks and compile a report. One inspection for each farm. Here are the tables:

INSPECTION

inspectionID.pk

farmID.fk

INSPECTION_LIST

inspection_listID.pk

inspectionID.fk

farmID.fk

block_number

My problem is in the relation between table INSPECTION and INSPECTION_LIST. I have a portal in a layout called Inspection to show INSPECTION_LIST lines and create records but I got the same information every line in INSPECTION_LIST. I want to enter the block # in the line and it would bring the information from the record in BLOCK, and I would enter the information about the block inspected.

Edited by Guest
Link to comment
Share on other sites

So if you type a block# into the Inspection-Line portal, you should get a unique combo of InspectionId-FarmID-Block#. If the records in the portal are the same, it is often because the fields have been miss-assigned; they should be either the TO of the portal, or a TO FURTHER down the relational line.

In order to see more Block info, you would need another TO of Block attached to Inspection-Lines. You cannot go BACK to the Block TO, because that passes thru Inspection, where the the block combo is not in any way unique (as block# isn't even there, and could not be). That could be the problem. You have to pass THRU Inspection-Lines to see data from the unique block.

Edited by Guest
Link to comment
Share on other sites

OK, I read and read your explanation and changed things around, now I have it done.

Now it is working but I don't know if it is a good design or just a bad but working design.

Fenton, I would like to hear what do you think.

newrelations.png

Edited by Guest
Link to comment
Share on other sites

This topic is 6106 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.