Database Structure, Relationships and More

Question

Hi all

I would greatly appreciate some help with a new database, to ensure that the foundations are sound.

The relationship map in its current form is posted here….

https://cpmcloud.sharefile.com/d-s7e789ea3a3394400be4ffbc1494f3d4b

The purpose of the database is to track user input to tasks by date and hours worked.  Each Contract we win comprises a number of Processes and each Process requires Tasks to be completed.

The Processes will repeat for different contracts, so there are Contract and Process tables linked by a Contract-Process table.

Similarly, the Tasks tend to repeat across Processes and Contracts, so there is a Task table linked to the Contract-Process table by a Contract-Process-Task table.

The time data is applied to each Contract-Process-Task using a one-to-many relationship from tb_Task-Date.  Similarly, the employee is assigned to each tb_Task-Date from table tb_Employee.  The final table tb_Discipline is simply storing the employee’s discipline or job type.

So the questions are:

Does the table structure and relationship arrangement appear to be correct based on the description above?

Also, I have trouble creating forms and queries that work with the many-to-many type relationships shown here.  Once I have populated the Contract and Process tables, for example, what is the best approach for assigning a particular Process to a Contract?

Many thanks to anyone that has managed to even read this far, let alone responded.

Phil

Answer 1

First, never ever use a - (dash) symbol in a table or field name. It is reserved symbol. ACCESS considers it an arithmetic minus and can cause sever problems later if you use it in as part of a table or field name. Only use a - minus symbol when you are subtracting one number from another. The ONLY symbol safe to use in a table or field name is a _ (underline). So change all the - symbols to _ symbols first before doing anything else. After that:

1. You don't need the tb_Process table. Put the fields in that table into the tb_Contract_Process table.
2. You don't need the tb_Task-Date table. Put those fields into the tb_Task table.
3. You don't need the tb_Contract-Process-Task table.
4. Rename any ID field to its respective table ID. For example, rename the ID field in the tb_Contract_Process table to ContProcessID.
5. Once all the table names and field names have been changed to remove the - symbols and the fields in the tables that are not required have been moved to the appropriate tables, then you can create the appropriate table Foreign Keys and create the new relationships.
6. You should have a tb_Contract table that can have multiple Processes in the tb_Contract_Process table which can have multiple Tasks in the tb_Task table which can have multiple employees assigned in the tb_Employee table which can have multiple disciplines in the tb_Discipline table (make sure you really need this table).

Answer 2

Lawrence

Many thanks for taking the time to provide such a detailed response.

Clearly I have lots to learn/relearn.  Will rework the structure to your recommendations and post back when I next get tied in knots.

Phil

Answer 3

Hello Lawrence

I have a problem with the table structure you have recommended.

The idea of the original arrangement would allow (I think) a table (list/library) of e.g. Processes to be established then subsequently linked to any future process via the Contract_Process table.

If I have a one-to-many directly between the Contract table and the Process table and enforce referential integrity, I can only add Processes that are linked to a given Contract. I am not seeing how I can re-use entries in the Process table for subsequent Contracts.

I hope that explanation is clear.  Is my understanding of Access correct here?

Thanks

Phil

Answer 4

Please can you upload a screenshot of your relationships here

Answer 5

Hello Isladogs

Thanks for your interest.

My first attempt at a suitable relationship was

https://cpmcloud.sharefile.com/d-s1efe308534424f93a5a89a42262de898

After input from Lawrence, I changed the relationship to

https://cpmcloud.sharefile.com/d-s53404c922803410b9da839fd89466e2a

Phil

Answer 6

Sorry but I'm not going to click on an external site to view these links

Answer 7

OK Isladogs

The links are legit, but completely understand your concerns. I'm still in News Group mode :)

My original relationship and the modified versions are attached in that order.

Phil

Answer 8

Phil, I was thinking something like this:

Answer 9

I agree with Lawrence

Your revised relationship diagram had two issues.

1. Tb_Discipline wasn't joined to tb_Employee

2. You hadn't set referential integrity between the joined tables

Answer 10

Thanks Lawrence

The reason my structure included many-to-many relationships was as follows:

The Processes can repeat for each contract, out of approx 20 Processes, 3 or 4 may be required.  For instance, we have Preparation and Extraction Processes which regularly form part of our contracts. So many Contracts have many Processes and many Processes have many Contracts.  The same applies for tasks, which involve e. g. creating layouts, building equipment lists and so on, again these are common to many Processes.

It is not clear to me why the adoption of many-to-many relationships is incorrect.  Can you explain please?

Phil

Answer 11

Many-to-many means nothing in a relational database. Relationships can be one-to-one or one-to-many. When I design a database table structure I think of when I delete a record in any table, what records also need to be deleted down the line in related tables. That thinking will dictate which fields in the tables will be related and therefore contain Foreign Key fields. But when you create the relationships in the relationships window in the beginning, it will always be one-to-one with referential integrity enforced. That is how my example is designed. Many-to-many is not an option.

Answer 12

Thanks for your input Lawrence (and Isladogs)

I will proceed on the basis of your relationship diagram.

Phil

Answer 13

I commend you for asking how to get the foundation structure properly done in the first place. Many don't do that and then later can't figure out what went wrong. It's like building a house. You need to get the foundation right from the start or the house cannot stand.

Answer 14

A couple of comments on one of Lawrence's recent replies.

Many-many relationships do of course exist in relational databases but should be handled via a junction table which has a one-many relationship with each of the main tables.

For example, in a school each class has many students and each student has many classes. So a junction table tblStudentClasses is used as an intermediary between tblStudents and tblClasses.

There are numerous similar cases handled in the same way.

Also when you create referential integrity on each of the relationships with one-many joins (not one-one as Lawrence wrote by mistake), I would also tick cascade delete and cascade update. For more details on relationships in Access, see my three part article Relationships and Referential Integrity

Answer 15

Sorry for the misunderstanding. Of course you can create one-to-many relationships in the beginning if you want to. Normally, it is one-to-one though in the relationships window at first.

Many-to-many relationships can only occur in a junction query as far as I know, not in the relationships window when you create relationships.

Sorry for the confusion.

Why did the font size change? Very weird.

Answer 16

Thanks for the clarification Isladogs.

I was wondering about the cascade delete and update options.

Phil

Answer 17

Sorry for the misunderstanding. Of course you can create one-to-many relationships in the beginning if you want to. Normally, it is one-to-one though in the relationships window at first.

Many-to-many relationships can only occur in a junction query as far as I know, not in the relationships window when you create relationships.

Sorry for the confusion.

Why did the font size change? Very weird.

In reverse order,

1. A site glitch means the font size is reduced when you delete a line. It happens to me a lot.

2. Many to many relationships are created in the relationships window in the way I described using a junction table. I've no idea what you mean by a junction query but any join in a query is just that. Query joins do not create relationships.

3. A one-one relationship is only created when the two joined fields are both unique and indexed. Typically this means both are primary keys. 

If a PK is joined to a foreign key, the relationship is one to many. If both fields are not indexed with no duplicates allowed e.g.neither is a primary key, the relationship is indeterminate and R.I. cannot be enforced.

All this and much more is covered in the article linked in my previous reply.

Answer 18

It is not clear to me why the adoption of many-to-many relationships is incorrect.

It is not incorrect of course.  What you cannot have, however, is a many-to-many relation object.  A many-to-many relationship type is always modelled by a table which resolves it into two or more one-to-many relationship types, as a has been described in this thread.  I'm ignoring the ability to model a many-to-many relationship type by a 'multi-valued field'.  Most experienced Access developers would eschew the use of such fields.

You'll have noted the 'or more' in the last paragraph.  A many-to-many relationship type can be of any number of multiple dimensions, not merely binary.  You might for instance have a ternary relationship type between orders, products and suppliers, modelled by an OrderDetails table which resolves it into three one-to-many relationship types.  In fact such a relationship type is really a little more complex, as to model it by a single table would mean every supplier is able to supply every product.  In reality this is unlikely to be the case, so the binary relationship type between products and suppliers would itself be modelled by a table which resolves it into two one-to-many relationship types.  The primary key of this table is a composite of the two foreign keys, ProductID and SupplierID.  The ProductID and SupplierID columns in OrderDetails are consequently a composite foreign key referencing the composite primary key, and the relationship between the tables would be created on the two pairs of columns, not on single columns.

You might like to take a look at Relationships.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how relationships are built up across a database to achieve the final model, using a simplified medical prescriptions database as its example.

---

Ken Sheridan, Stafford, England

Answer 19

I meant a Union query not a Junction query.

Answer 20

I meant a Union query not a Junction query.

A union query doesn't involve a many-many relationship either. It is just two or more separate queries run together to 'combine' the results. In fact the separate sections of the union are not joined at all

Answer 21

Thanks for the clarification Isladogs.

I was wondering about the cascade delete and update options.

Phil

Please read my article if you need further explanation of what each of these is for.

Answer 22

Thanks Ken

That Relationship.zip example is outside my comfort zone, but the explanation is a great help.

Phil

Answer 23

Apologies. This is partly to check if this forum is still active as there have been no posts since the announcement about the forum moving to a new home.

Having checked, there is no equivalent location in the Microsoft Q&A site. See my question about it at Where is the Access for Developers forum?

Anyway, I hope you've managed to sort out your issues with relationships etc