Optional foreign keys

[MHL]

Greetings.

I’m trying to figure out what the data modelling best practice is, regarding a situation where a table can be connected to several others using an “optional” foreign key.

To better illustrate the example, let say we have a TASKS table.

Each task is assigned to a user from the USERS table.

Each task can be related to either a Client, a Supplier, a Partner or a Prospect. That means at least 4 tables. CLIENTS, SUPPLIERS, PARTNERS, PROSPECTS

In this scenario, should I spit the tasks into different tables depending on their category? Use a union query to create reports and forms from a User (the person that the task is assigned to) point of view?

Or keep all the tasks in one table to be easier to manipulate the data? What about referential integrity between tables in this case? Perhaps creating a junction table?

Pros and cons of each approach?

Some guidance would be highly appreciated. I’m quite new to access and so far I’m loving it. Feels like playing with Legos to do grown up stuff!

 

[sneuberg]

I suggest one table for the CLIENTS, SUPPLIERS, PARTNERS, PROSPECTS let's say tblUser and have a field in this table that would have a field perhaps called User Type which would contain either CLIENTS, SUPPLIERS, PARTNERS, or PROSPECTS.

 

[MHL]

Just added two attached examples.

In one the tasks are split into different tables. In the other there's an added Category field to differentiate the tasks. (not even sure it will be necessary)

In both examples Referential Integrity is enforced.

Which one is more correct / practical ? Any alternatives that I'm not aware of?

 

[plog]

Neither. You shouldn't have tables with the exact same structure. When you do that you are essentially using the table name to store data that itself should be stored in a field.

All your CLIENTS, SUPPLIERS, PARTNERS, PROSPECTS data should be in one table, with an additional field to distinguish if they are a CLIENTS, SUPPLIERS, PARTNERS or PROSPECTS. That gets those 4 tables down to one, and it also makes you simply have 1 Task table.

 

[MHL]

Neither. You shouldn't have tables with the exact same structure. When you do that you are essentially using the table name to store data that itself should be stored in a field.

All your CLIENTS, SUPPLIERS, PARTNERS, PROSPECTS data should be in one table, with an additional field to distinguish if they are a CLIENTS, SUPPLIERS, PARTNERS or PROSPECTS. That gets those 4 tables down to one, and it also makes you simply have 1 Task table.

Indeed. That's definitely a possibility. But for the sake of argument, let's say that the tables structure is significantly different. eg, PROSPECTS only contains basic contact information, while SUPPLIERS have a considerable amount of extra fields.

Or that they all have different tables linked. SUPPLIERS will be linked to an ORDERS table, while PROSPECTS is not.

Or perhaps a PARTNER is related to a CLIENT in a one to many relationship.

Basically, something that justifies having them split into different tables. In that case, what would it be the best approach?

 

[gemma-the-husky]

if the single table suggestion doesn't work - then maybe the problem is with the "user"

ie. I struggle to understand how a "task" can be allocated to a "user", when the users are not of a similar type.

 

[MHL]

Assigning a user to a task is not a problem. That involves a one to many relationship and that's it.

The issue is assigning a task to a category (ie. a Client or a Supplier, different tables, different foreign keys) while maintaining the relationships referential integrity. That's what I'm trying to figure out.

 

[Solo712]

Assigning a user to a task is not a problem. That involves a one to many relationship and that's it.

The issue is assigning a task to a category (ie. a Client or a Supplier, different tables, different foreign keys) while maintaining the relationships referential integrity. That's what I'm trying to figure out.

I looked at the examples and they are pretty rudimentary. In a full-blown app, the decision whether to go with four tables or a single one with a user type field would depend how many specific attributes each type of user has, whether the types are persistent and whether there are relationships among them. If there are indeed many specific fields, or dependencies you would want to consider four tables and UNION queries for the tasks. I use a technique of assigning a common key to tables in a union, to do searches by and to avoid duplicate references. In your case you would assign a unique "tasker" key to each new record in CLIENTS, SUPPLIERS, PARTNERS, AND PROSPECTS.

Best,
Jiri

 

[MHL]

I looked at the examples and they are pretty rudimentary. In a full-blown app, the decision whether to go with four tables or a single one with a user type field would depend how many specific attributes each type of user has, whether the types are persistent and whether there are relationships among them. If there are indeed many specific fields, or dependencies you would want to consider four tables and UNION queries for the tasks. I use a technique of assigning a common key to tables in a union, to do searches by and to avoid duplicate references. In your case you would assign a unique "tasker" key to each new record in CLIENTS, SUPPLIERS, PARTNERS, AND PROSPECTS.

Best,
Jiri

Yes, they are simple examples for illustrative purposes only.

I've been reading quite a bit on database normalization and going through some examples but couldn't find anything similar to this case. So I was wondering if it would be possible to have optional foreign keys while maintaining referential integrity. Hence the tasks scenario.

I figured the separate tables and the UNION query would be the "correct" way of doing it. It also offers the advantage of having a lower number of records per table.

Thank you so much for all the replies folks.

 

[The_Doc_Man]

My concern is that you have a disparate group of task types. The question is whether your reports from such a wide variety of task types could be formed into something coherent - and whether it SHOULD be combined at a detail level. Let me try to express this more clearly because I'm not saying you can't do this. I'm going more in the direction of looking at this from a higher level.

When you assign someone to a task and want to report on who is assigned to a task, do you really need more than rudimentary details of that task (regardless of its type) in that report? If not, then your UNION query is almost certainly the way to go and you can have your four separate tables of tasks to minimize complexity and minimize the occurrence of too many nulls in the hypothetical "combined table" that would have been used to describe everything in a single data call.

But this is the principle I wanted to illustrate. I answered the question not based on the data but based on the output requirement. You said you were new to Access. Here is the way to look at the problems.

I always use the "Old Programmer's Rules" to explain what you need to do.

Rule 1 - If you can't do it on paper, you can't do it in Access. Meaning, analyze your data flow, data sources, etc. until you have identified how data gets into your database and how it gets out and what happens to it in between. Know what you are going to do BEFORE you try to do it. Because if you don't understand the problem well enough to draw out a diagram of it on paper, Access won't help you.

Rule 2 - Access can't tell you anything you didn't tell it first. Meaning, you are building this application for a PURPOSE. Focus on the purpose as a goal to be reached and work backwards from there to see what you need to capture to reach that goal. I.e if you want the total number of eggs laid by your chickens in a given week, you need to input the number of eggs laid by each chicken on each day to get the totals. If you didn't count the eggs, you can't get the total.

In the final analysis, though each of us has done a lot of database (and other) design tasks, it will be YOUR problem and therefore YOU will have to decide what will or won't work. We don't know your situation in enough detail to KNOW what needs to be done, we can only offer guidelines based on what you tell us. (This fits in with the 1st rule above, by the way.) Having said that, I suggest that you do some reading on the subject of database normalization (which can start on a Wikipedia article, but shouldn't stop there).

You asked about referential integrity. Your CLIENTS, SUPPLIERS, PARTNERS, and PROSPECTS entries are in four tables, so an RI type of relation could not be established in a common junction table with one pointer. It would be another case where a UNION query would be needed because you would need four types of assignment (to have RI with their corresponding underlying tables) that would participate in the UNION.

 

[MHL]

My concern is that you have a disparate group of task types. The question is whether your reports from such a wide variety of task types could be formed into something coherent - and whether it SHOULD be combined at a detail level. Let me try to express this more clearly because I'm not saying you can't do this. I'm going more in the direction of looking at this from a higher level.

When you assign someone to a task and want to report on who is assigned to a task, do you really need more than rudimentary details of that task (regardless of its type) in that report? If not, then your UNION query is almost certainly the way to go and you can have your four separate tables of tasks to minimize complexity and minimize the occurrence of too many nulls in the hypothetical "combined table" that would have been used to describe everything in a single data call.

But this is the principle I wanted to illustrate. I answered the question not based on the data but based on the output requirement. You said you were new to Access. Here is the way to look at the problems.

I always use the "Old Programmer's Rules" to explain what you need to do.

Rule 1 - If you can't do it on paper, you can't do it in Access. Meaning, analyze your data flow, data sources, etc. until you have identified how data gets into your database and how it gets out and what happens to it in between. Know what you are going to do BEFORE you try to do it. Because if you don't understand the problem well enough to draw out a diagram of it on paper, Access won't help you.

Rule 2 - Access can't tell you anything you didn't tell it first. Meaning, you are building this application for a PURPOSE. Focus on the purpose as a goal to be reached and work backwards from there to see what you need to capture to reach that goal. I.e if you want the total number of eggs laid by your chickens in a given week, you need to input the number of eggs laid by each chicken on each day to get the totals. If you didn't count the eggs, you can't get the total.

In the final analysis, though each of us has done a lot of database (and other) design tasks, it will be YOUR problem and therefore YOU will have to decide what will or won't work. We don't know your situation in enough detail to KNOW what needs to be done, we can only offer guidelines based on what you tell us. (This fits in with the 1st rule above, by the way.) Having said that, I suggest that you do some reading on the subject of database normalization (which can start on a Wikipedia article, but shouldn't stop there).

You asked about referential integrity. Your CLIENTS, SUPPLIERS, PARTNERS, and PROSPECTS entries are in four tables, so an RI type of relation could not be established in a common junction table with one pointer. It would be another case where a UNION query would be needed because you would need four types of assignment (to have RI with their corresponding underlying tables) that would participate in the UNION.

Thank you so much, The_Doc_Man. Sound words of advice there.

Following your and Solo712's advice, I've used a UNION query in order to keep RI between tables in the example attached. (only used two tables, Clients and Suppliers, but the principle is the same for extra tables)
However, I think this way it works without the need of adding an extra unique field to each record like suggested. And I didn't have any issues with null fields.

One way or the other I can make it work, but from a data modeling point of view I'm not sure it's the correct way. That's what I'm trying to figure out.

Like you said, you folks have lots of experience, that's why I'm here picking your brains. Would buy you a beer if I could
cheers.