Multiple one-to-one relationships from a single table

[FeederPig]

My organization performs food inspections and sanitation inspection. anreports for multiple customers. We generate a report for each inspection. The heading and body of the reports are different but the final section, which contains electronic signitures is always identical. I use one-to-many relationships to link customer information in the heading to the different report body tables. I could create identical fields for signitures in each report body table but I want to avoid duplicate fields in multiple tables. I want a single table for signitures. The signiture blocks serve a function internal to my company. They have no functional relationship to the customer. Since each report has only on signiture block and each signiture block applies to only one report a one-to-one relationships sounds right.

Does this table structure make sense? One-to-many relationships between CustomerDataTable and both FoodInspectionTable and SanitationInspectionTable. One-to-one relationships between FoodInspectionTable and SignitureTable, and SanitationInspectionTable and SignitureTable.

CustomerDataTable
CustomerID(pk)

FoodInspectionTable SanitationInspectionTable
FoodInspID (pk) SaniInspID (pk)
CustomerID CustomerID
-data fields- -data fields-
SignitureID SignitureID

SignitureTable
SignitureID (pk)
-signiture fields-

 

[Atomic Shrimp]

Have I got this right?

There are many customers
Each customer may be inspected many times, each inspection generating a report
Each report may include a block of text picked from a list of standard texts

If so, then yes - you want a table of customers, a table of inspections/reports and a table of standard texts.
But the relationship between tblInspections and tblSignatures isn't one-to-one, because each text/signature may be referred to by many different inspection reports.

If the above is a reasonably accurate description of your situation, I would say you've got your data structures right.

 

[Atomic Shrimp]

On re-reading your post, it seems like you might be saying that the signatures are unique per report (rather than being standard text items that may be chosen to appear on other reports again in the future).

If this is so, then there is simply no need to keep them in a separate table at all.

 

[FeederPig]

I have been pondering your response. I think I see a different way to solve my problem. I will try to explain. Yes, there are many customers. Each customer receives many reports but each report applies to only one customer.

Customer
CustomerID (pk)

Report1
Report1ID (pk)
CustomerID

Report2
Report2ID (pk)
CustomerID

I actually have six inspection reports, each with a unique set of inspection findings, but two will work for illustration purposes. I link each of the six report tables to the one customer table by one-to-many relationships. A query leads to a report that I give to my customer.

I want to expand the use of my database to perform management and quality control functions on my emplpyees. My old paper form has blocks for the signiture of the inspector, the supervisor, and the manager. I though one set of signitures was unique to one report, but I am wrong. Any one report can have only one inspector, supervisor, and/or manager signiture. But any one inspector, supervisor, or manager will sign multiple reports and not all reports have three signitures. All have an inspector signiture. About half are reviewed and signed by a supervisor. Less than a third are monitored and signed by a manager. If I put all the signitures in one table, I will have a lot of empty fields and duplication. What I need is three separate signiture tables each linked to the report tables.

Report1
Report1ID (pk)
CustomerID
InspectorID
SupervisorID
ManagerID

Report2
Report2ID (pk)
CustomerID
InspectorID
SupervisorID
ManagerID

Inspector
InspectorID (pk)

Supervisor
SupervisorID (pk)

Manager
ManagerID (pK)

Does this sound workable?

 

[Atomic Shrimp]

To be honest - no. It looks like you're designing table structures that duplicate each other and would be better stored together in fewer tables.

Suppose, for example, there are six different types of report, you don't need six tables to store records for them you need one table, with an extra column to identify what kind of report is described by each row.

Similarly, it doesn't seem to make sense to store your people in different tables according to role - just put them all in one table 'people' and have a column that contains 'manager', 'inspector', etc to describe each person's role.

If I put all the signitures in one table, I will have a lot of empty fields and duplication.

I don't think you will - I just think you need to work out how to store it optimally.

If Fred Bloggs has an Inspector signature and a manager signature, and Jane Doe has a supervisor signature and an inspector signature, you could describe that in a table like this:

Employee, SigType, Signature
Fred Bloggs, Ins, {Fred's Inspector Signature here}
Fred Bloggs, Mgr, {Fred's Manager Signature here}
Jane Doe, Ins, {Jane's Inspector Signature here}
Jane Doe, Sup, {Jane's Supervisor Signature here}

The next stage is to look at repeating data in that table - and notice that it isn't necessary to keep repeating names - you could have a table that just describes people, then put the ID value for each person in the 'employee' field in the above table. Same with signature type - have a separate table of signature types and just refer to the type by ID in the above table.

This process - called normalization - is probably the most commonly discussed topic on this board, and rightly so - it's probably the most important part of database design - get this bit right and most of the rest just falls into place.

 

[FeederPig]

I agree about normalization. I have been working on my table structure for three months now and have revised almost every table and the relationships at least once. My six reports are truely six very different reports. The only thing they all have in common is the name, location, and contact information for the customer. No two reports have similar main bodies, which is why each report has a table to store the main body information. The only thing my report tables have in common is the customerID so I can link them to the customer table.

I had not thought of categorizing the signitures like you suggest. I will play with that. Thank you for getting me thinking in a new direction.

 

[Atomic Shrimp]

Fair enough. The report data could probably still be stored in a single normalized table, but the effort may only be worth pursuing if there might be a seventh, eighth etc report type in the future.

So (treating this more as a theoretical exercise now) for example, instead of a table that has all of the report data fields on one row, you could have a report header table, describing the customer, the report ID, date, etc, then a report detail table in which the report data items were stored like individual lines on an invoice - each line containing an identifier to denote which part of the report it is, and a bit of actual report data.

 

[FeederPig]

I like academic discussions. You come pretty close to how my tables have developed. I have a Customer table and a Site table (they have a many to many relationship) that contain most of the information in the report header. The body of the report comes from the individual report tables.

I am working my way through Access 2007 Inside Out. According to the book, breaking tables down into parts that each contain information related to one thing is best. They say that minimizing empty fields in table is best. One problem I struggled with is that each of the four types of food inspections we do have two parts. Most inspections find no defects and that is the end of it. But when defects are found, those defects generate a whole new set of data. I deal with this data in another table linked to my primary inspection table. If I put all of these fields in one table, most of the time all of the fields related to defects are empty. Putting my defect data in a separate table lets me avoid empty fields in a single table and I only need to create a record in the defect table if a defect exists.

One of the difficulties I deal with are what I call supporting tables. These a basically look up tables that contain predicatable information provided by the customer. These come into play with the defect tables. My customers have their own defect criteria and codes that they want used in the defect portion of a report. My customers pay their suppliers based on my inspection results and there is a lot of money involved. Seldom is a delivery with defects rejected. Most often there is a price adjustment. I am finding that there is more than one way to look up information. I use a combo box when only a few choices exist and they are unlikely to change, but most of the time I put the information in its own table.

Now I will work on getting my signiture tables worked out so I can do some internal management control. Anyway, thanks again for the advice and input. By the way, I like the Snow Pea boat. You have some interesting interests.

 

[The_Doc_Man]

I might be able to clarify just a tiny bit.

There are many reasons to bring together things that belongs together, but there are also a few reasons to keep things split. It is possible that your signature block discussion is one of the VERY RARE cases where a one-to-one relationship is reasonable.

Under most circumstances, the general design rule is that if a data element depends on the same exact key as another data element, where both elements are relating to the same interpretation of that key, they belong in the same table. The goal is that all items using the same exact interpretation of the same exact key belong together.

However, exceptions occur when some of the data elements have different security requirements than other elements, even if they meet the above criteria. In my job with the U.S. Dept. of Defense, we see cases where certain data elements are kept separately because of security reasons even though it results in a one-to-one table relationship. I believe this could possibly apply in your case, though I wouldn't swear to it. The alternative is to have the table contain the signature block but have a SELECT query that shows you everything EXCEPT that block. Base your less secure forms off the query and the more secure forms off the table. (Or, for really PURE purists, off a different query that includes everything.)

Another reason to allow one-to-one table relationships is that rare case where you have at least THREE segments that each are quite large but you only work with one or two at a time. We have that in some of our military personnel tables having to do with spouse information as an extension of a personnel record and also with contract info (because reservists sign a service contract in the U.S. Navy).

We rarely use all three parts at the same time. Often we use just the base table joined to something else. Sometimes, for benefits, we join to the Spouse tables. Sometimes, when considering whether the person is fulfilling the contract, we join to the contract (terms) table. Those are one-to-one cases.

The basic concept of DB design is always to let form follow function. Therefore, we will be able to agree or disagree on what you tell us only if you present the problem clearly. YOU are the person who really knows the problem. We are just a bunch of onlookers. We can (and usually do) advise you when what you tell is going down a garden path, but only you know whether what you've got meets your needs. So always remember to treat this forum as advisory, never stronger than that.

 

[PaulWilson]

The Doc Man makes a point I've seen in various books and postings.
However, exceptions occur when some of the data elements have different security requirements than other elements, even if they meet the above criteria. In my job with the U.S. Dept. of Defense, we see cases where certain data elements are kept separately because of security reasons even though it results in a one-to-one table relationship.
-------------------
I wonder how, in fact, the removal of SSNs from a contacts table and placement in a separate table makes them more secure. Is it just to avoid a clerical mistake like grabbing all the fields in the contacts table using the * operator in the Query design window? Or is it to simply hide that table in the Nav pane? Or is there some security feature I don't know about?

 

[jdraw]

FeederPig,

I think the info/advice from Atomic Shrimp and theDocMan are all on target. In addition I would suggest you read the first few topics at this site dealing with Normalization and Entity Relationship Diagramming:
http://www.rogersaccesslibrary.com/forum/topic238.htm

Work through your data and rules to create a model. As theDocMman said, you know the data and your situation, the rest of us are onlookers.

I would also advise you to list many of the business rules/facts associated with your "application". Create some test data and play "stump the model" -- that is see if your "evolving ERD or data model supports the data and the facts. If you find any discrepancies, review them and adjust as necessary. (Could be bad data; could be a hidden fact that comes to light...) You'll be surprised at how much you will learn about your data and related facts-- and all the time you'll be learning "why normalization" is a key to tables and relationships.

There are a number of free data models at www.databaseanswers.org many of which have a list of business rules/facts (to some degree) and may be helpful to you.

Good luck with your project.