RI to more than one table

[jason_nevin]

I have two tables tbl_engineer & tbl_user. Potentially the people in either table can be trained. I'd like to store details of this training in tbl_training. So my question is; How do I create a relationship from tbl_training to both tbl_engineer & tbl_user that ensures RI?

 

[Rabbie]

It is often possible to combine/merge tables such as your tbl_engineer & tbl_User and use a status field to differentiate between users and engineers. Then it would be simple to set up the relationship.

 

[jason_nevin]

Unfortunately not in this case because both are entirely different entities in terms of their content, parent tables, etc. Its simply a case of having two tables that happen to have the same relationship with another table.

 

[KeithG]

How do I create a relationship from tbl_training to both tbl_engineer & tbl_user that ensures RI?

tbl_training wolud be the child table so you would be creating a relationship from tbl_engineer and tbl_user to tbl_training. Why not just setup two one to many realtionships from your tables to your training table? Are you wanting a one to one relationship? It sounds like it but I don't think that would work for you because you need a record in both table to enforce RI. So for example as soon as you enter a new engineer a record would need to be created in the training table, otherwise you would be violating RI.

 

[jason_nevin]

Is it possible to have two 1-2-many relationships from a single table? Presumably you would need two foreign keys but one would always be null. Is that possible?

 

[Rabbie]

Is it possible to have two 1-2-many relationships from a single table? Presumably you would need two foreign keys but one would always be null. Is that possible?

Yes its possible but you have to allow for null links when using these tables in a query.

You will have entries in training with no entry in engineers and other entries with no entry in Users.

 

[The_Doc_Man]

Unfortunately not in this case because both are entirely different entities in terms of their content, parent tables, etc.

Are you familiar with the phrase "Pay me now or pay me later." ??

This is a MAJOR design flaw in your system. Fix this NOW so that you have a PERSON table that is the same for all persons. You split out "PERSON" information from each of tblEngineer and tblUser so that each has a pointer to a person. The stuff that is truly unique for engineers vs. users stays in the tblUser or tblEngineer tables.

Then make your relationship be "person" to "training" and take advantage of the transitive nature of the engineer-person-training or user-person-training relationships.

Otherwise you are going to give yourself headaches. Including, what do you do when you have a person is both a user and an engineer. (Don't say it never happens. As soon as you do, Murphy's law says you'll find at least three.)

 

[jason_nevin]

Surely the engineer-person and user-person relationship has the same problem but one level higher. i.e I still have one child table (in this case person) with two relationships to seperate parents (user and engineer).

 

[The_Doc_Man]

Not when it counts. You don't need to know whether a person is a user or an engineer to record the training info. You don't need to know whether a person is a user or an engineer to enter basic personnel data.

AND ... you can actually treat this the other way around from the image you are painting. PERSON is the parent and you have two one-one relationships - but SPARSE one-one - between person/user and person/engineer. You then store the unique user info in the user table as a CHILD of person. You store the unique engineer info in the engineer table as a CHILD of person. (This is one of the rare cases where one-one tables make sense.) Since these are SPARSE tables, you can have queries that associate a person to the USER table and that separately associate a person to the ENGINEER table. If it happens that a person is both an engineer and a user, that person is returned by both queries.

Training is just another child table of person - in this case NOT segmented into user and engineer groups.

 

[Banana]

AND ... you can actually treat this the other way around from the image you are painting. PERSON is the parent and you have two one-one relationships - but SPARSE one-one - between person/user and person/engineer. You then store the unique user info in the user table as a CHILD of person. You store the unique engineer info in the engineer table as a CHILD of person. (This is one of the rare cases where one-one tables make sense.)

and didn't we talk about how such one-one relationship can be actually subtle denormalization?

I'm not 100% sure if I understand Jason, but it sounds as if training may be dependent on whether the person is a User or a Engineer. If this is indeed the case, then I would have the training depend on the two key (Person and Classification of User/Engineer) joined together.

 

[The_Doc_Man]

Banana, you are absolutely right - but I infer from what I'ver read that Jason might not yet be ready for the magnitude of the solution you had to consider. From our discussion, I know you WERE ready. Further, with Access, if you get to 3rd normal form, you are usually pretty well off. He can do that with what I suggested. You will recall your solution was either 4NF or 5NF?

Down the road, Jason, you need to read an exchange Banana and I had in the "Theory and Design" category. Went on for about three or four pages. However, it is very heavy reading. No disrespect intended, but the nature of your question makes me wonder if you are going to be comfortable with that material. It is subtle and I've been known to be wrong in my "take" before.

If Banana's surmise is correct and the nature of the training depends on the person's role, you have a real mess on your hands. On the other side of that coin, if the same training is supplied to both users and engineers, then my take is closer to correct.

 

[Rabbie]

If Banana's surmise is correct and the nature of the training depends on the person's role, you have a real mess on your hands. On the other side of that coin, if the same training is supplied to both users and engineers, then my take is closer to correct.

However if the training for Users and Engineers is sufficiently different with different attributes then ther could be a case for having two tables tblEngineerTraining and tblUserTraining. It depends on information we do not have to hand but Jason will know. Not an approach to please the purists but Access is about enabling people to solve their problems.

 

[The_Doc_Man]

Rabbie, absolutely correct again on two particulars.

First, if Jason's training depends on the category of person taking it, then training is not uniform and it is POSSIBLE (not guaranteed) that two tables would be proper.

Second, only Jason can decide that.

I fear, however, that being inexperienced, Jason might not yet know the questions to ask in order to uncover this particular nuance. Jason, if you are following this, it is a really razor-thin fine point. If users and engineers sit side-by-side in the same training classes and get the same certificates, then training is a PERSON issue and should be a child table of a person table. If users and engineers take classes with similar names but do not sit in the same sessions and get the same exact training, then you have a more complex case that needs to be addressed differently. Just HOW differently depends on the differences in their training and how those differences are to be recorded. I.e. we would need more explanations on that front in order to guide you better.

 

[jason_nevin]

My original database design was such that each person type table i.e user, engineer, administrator had their own table (tbl_user, tbl_engineer and tbl_administrator). Each table had a one-to-one relationship with a table called tbl_address and a one-to-one relationship with a table called tbl_login. I did it this way because address information was common to every user table (and others - tbl_site, tbl_customer) and so was login information. I then set up a one-two-many relationship from tbl_login to tbl_training.

I wasn't sure how to enforce a one-to-one relationship in SQL server (I'm new to it) so I asked how on a SQL server forum. The reply I got effectively said that my design was flawed and I should include login and address information in my person tables. So I took this onboard and started to redesign the database but then came accross my training table hitch. Now, I realise that this is an Access forum but the theory is the same, right?

So, are you guys saying that I was right in the first place?

 

[jason_nevin]

BTW. In my design I created a relationship from tbl_user, tbl_administrator and tbl_engineer to tbl_login by including a foreign key column - FK_tbl_login - in each of the tables.

 

[The_Doc_Man]

So, are you guys saying that I was right in the first place?

No, just that we can't ALWAYS tell when you are wrong.

See, the issue is that data intent and usage will define the "right" structure or will show us and you when you have the "wrong" structure. But in general, there is no right and wrong until you actually try to do something with what you have. THEN is when all sorts of nasties jump out of the woodwork. THEN is when you get bitten on the butt.

All we can advise is that for a TYPICAL system, one structure vs. another is more likely to be more effective (and easier to manage) than another structure. One structure is more likely to be more accurately representative of your problem than another. We don't know the ins and outs of your problem like you do, and it is always, ALWAYS, ALWAYS true that data drives the design, not the other way around.

Having said that, it is my (personal) opinion that a split between users and administrators is a difficult structure because unless you carefully refine out the common parts to a common table, you will have to look too many places at once for some data elements. Note that I did not say WRONG structure. I think it introduces complexity that does not model reality as you have described it in words. The questions I have asked in earlier posts of this thread were intended to elicit pertinent facts to support decisions about which design options would be easier or harder to manage.