Design/Relationships

[j9070749]

Hi,



I am getting slightly confused with the relationships in my database. I was hoping someone could point me in the right direction.




First of all, do the relationships look as they should? tbl_loopcheck is the table I use to link all of the data, there is one loop check per tag, and each tag. Each loop check can have one mechanical check, one preliminary check, one function check part a and one function check part b.

Below is the query for the report, which works fine and fills all of the information in as it should (they are some extra tables in this diagram).



I have a form for entering data, with a tab for each section. I will have a button on each tab which will insert the data for the checks.

However, if I need to edit any data, I can't base the form on the same query I use for the report as it is not updateable due to the join types. So I re-designed a query (shown below) which I could base the form on to edit the data. This works, but needs all the relevant data in the tbl_loopcheck fields (Section1ID, Section2ID etc). I can't see an easy way of inserting this information in here.



Could someone give me some general advice on the design of the relationships and the best way to go about inserting data in the forms.

Many thanks in advance for any help.

[ItsMe]

I did not look at your relationships too closely. In general, it is not uncommon to have more tables in a query used for reports vs. a query used for data input.

If I need to build a form to add a record, I will typically use comboboxes to create a certain criteria. I will then apply this dynamic criteria to a query that, by itself, is updatable. The updatable query will not include the tables that the comboboxes are based on. Instead, the foreign keys within the query are matched (equaled) to the primary keys I retrieved from the comboboxes. It is the criteria collected from user input that is placed in a WHERE clause and added to the SELECT query. This insures a recordset that is relevant and updateable.

The table receiving the new record will have many columns that hold foreign key fields. This is the critical part of normalization. You need a table of relations to hold the activities of operations. Any time an event happens, the user updates this table and uses several FK's to do so. Other tables, that do not regularly receive updates will have their PK values placed in the relative FK fields of the "event" table. A table that records events should not be intuitive if opened in "View" mode. Many of the fields should hold number values rather than intuitive text.

[Dal Jeanis]

tbl_loopcheck is the table I use to link all of the data, there is one loop check per tag, and each tag. Each loop check can have one mechanical check, one preliminary check, one function check part a and one function check part b.

Your diagram shows a one-to-many between loopcheck and mech check, prelim check, part a and part b. If they are truly one-to-one, then there shouldn't be any fundamental issue with updatability. I would suggest changing the name of the autokey fields from ID to something more descriptive ACheckID, BCheckID, PCheckID and MCheckID for example. Otherwise you'll have to fully qualify them in all the joins in order to make them updateable. Although, technically, you could also make the Inspection ID foreign key on the other tables indexed, required, and unique, and that should allow updating.