Solved Best practices -- One-to-One table vs keep in same table (1 Viewer)

[stardustvega]

I'm working on restructuring an Access database at work, and I'm curious if there's a 'best practice' in this scenario.

In this database, there's a table of customers and a table of reps.

All active customers get listed in our directory with a primary rep. There can only ever be one directory rep per company.

What I'm wondering is if it's better practice to keep all the directory info in the customers table, like this:

TblCustomers:

Customer_ID	Customer_Name	Dir_Rep_ID	Dir_Show_Email	Dir_Show_Cell
1	Sample Co	23	TRUE	TRUE
2	Lorem, Inc	43	FALSE	TRUE
3	Ipsum LLC	72	FALSE	FALSE

Or whether it'd be better to put the directory info into its own table with a one-to-one relationship to the customers table, like this:

TblCustomers:

Customer_ID	Customer_Name	Dir_Info_ID
1	Sample Co	3
2	Lorem, Inc	4
3	Ipsum LLC	5

TblDirectoryInfo:

Dir_Info_ID	Rep_ID	Dir_Show_Email	Dir_Show_Cell
3	23	TRUE	TRUE
4	43	FALSE	TRUE
5	72	FALSE	FALSE

If I understand correctly, both options would comply with at least 1st and 2nd normalization. Here's my thoughts on the pros and cons of each:

Option 1 (keep it all in TblCustomers):
1) Pro: Makes queries simple. I won't need a join to retrieve directory data.
2) Con: Adds to the size of the TbleCustomer. Makes it a little harder to read.
3) Con: Most queries that use TblCustomers won't use the directory info data, which is only used when we create the directory once a year.

Option 2 (split it into two tables):
1) Pro: More compact and readable tables.
2) Pro: Keeps only the most regularly used info in TblCustomers.
3) Con: A join will have to be used any time that we do need directory info, including in the main data entry form (since users will select a rep to be the directory rep).

 

[ebs17]

when we create the directory once a year

Why would such a thing be necessary if the contacts remain constant. Why would once a year be the right time?
For a reasonably historical view (history), overwriting data is an information-destroying method.

I think a one to many relationship would be more appropriate. The current contact person would be identified by a timestamp of insertion or a flag as default.

 

[stardustvega]

Why would such a thing be necessary if the contacts remain constant. Why would once a year be the right time?
For a reasonably historical view (history), overwriting data is an information-destroying method.

I think a one to many relationship would be more appropriate. The current contact person would be identified by a timestamp of insertion or a flag as default.

Re: Why is once a year necessary?

Sorry, I see the confusion. We physically print a directory once per year (old school, I know) so we only use this data when we pull the info for the print run. For this reason, we *only* care about the person the customer currently wants to have printed in their directory at the moment in time when we run the relevant reports. I do understand what you mean and in theory we could do it this way, but I can't think of any reason we would ever need to know who was printed in the directory in some previous year. This particular data isn't used for anything else.

Edit: In all other contexts, if I had to send out an email blast, for instance, I would just query to get a list of all reps associated with an active customer. The relationship between the customer table and the rep table is a one-to-many relationship, since there can be many reps for any company. However, each company just gets one rep to put in the print directory.

 

[ebs17]

You asked about best practices. This will relate to general and universal requirements.

Highly specialized requirements can justifiably deviate from this, if only because of the possible simplification.
=> If all fields are in a table, this does not mean that you always load all fields into the query. Best practice here would be to use lean recordsets and thus only load the fields that you really need, a "SELECT * " is what you do NOT use.

 

[The_Doc_Man]

Regarding one-to-one relationships: If relational integrity is involved, it may be impossible to update such tables, since a formal relationship between two tables would require that for every entry in table A there would have to already be a corresponding entry in table B, and vice versa. This becomes incredibly difficult. If there is no relational integrity, it becomes less difficult to manage. But that means you have lost one of the good tools of a relational database if you can't enforce a relation.

There is a theoretical barrier to this as well. The hard rule is that a dependent field must depend ONLY on the primary key associated with the table holding that dependent field. In theory, if a given property/value/field depends on a particular prime key and you split that table, you have now violated the dependency because now a field in B used to, but no longer ONLY depends on its original prime key in table A. It now also depends on whatever factor made you move it to table B. In other words, the items in table B that depend on a COPY of the original prime key no longer mechanically depend on it. You have in essence created a new dependency that was not present before the split.

The general rule is that anything that depends ONLY on a particular key must be kept in the table defined through that key. Otherwise, you invite desynchronization since you might delete a record in A without affecting B - even though there was originally a dependency. And as noted earlier, you can't have one-to-one relational integrity, which means that such deletion CAN occur.

The ONLY time I've ever seen a valid case for a one-to-one relationship was when the table was too wide for the table structure limits or when some of the data had different security requirements than other parts.

 

[stardustvega]

@The_Doc_Man Ah, I see. I didn't realize you couldn't enforce referential integrity with a one-to-one table. Thank you for the information!

@ebs17 Thank you for the clarification. That's what I'll do then.

 

[ebs17]

you couldn't enforce referential integrity with a one-to-one table

I disagree.

 

[Pat Hartman]

You said there can only be one rep for a company but you never said that there can only be one company for each rep. REALLY. If you get a new customer, you hire an additional rep?

In a 1-1 relationship the "1" side - this is the side that defines the entity - is the parent and has the autonumber as a pk. RI is enforced and works exactly as it does with a 1-m relationship.

However, under NO circumstances would this be a 1-1 relationship. Rep is an entity in its own right and always deserves a separate table.

Typically 1-1 relationships are made for three reasons.
1. some columns need a higher level of security.
2. some columns are null more often than not.
3. too many columns for one table - this is usually a design flaw.

 

[Zidar]

Quick response:

1) If Dir_Rep_Id (and the rest of fields) must exist for each customer, use one table with declaring Dir_Rep_Id.Required = Yes​

2) If Dir_Rep_Id is not mandatory, then you must have two tables tblCustomers and tblDirectoryInfo.​

tblCustomers will look like {CustomerID, Customer_name) PK = CustomerID
tblDirectoryInfo should look like {CustomerID, Dir_Rep_ID, Dir_Show_in_Email, Dir_Show_Cell) PK = CustomerID
if Dir_Rep_ID, Dir_Show_in_Email, Dir_Show_Cell are all mandatory in tblDirectoryInfo record.

That is 1:1 relationship.


Explanation:

One table: If Dir_rep_ID, Dir_show_Email, dir_Show_Cell are mandatory fields, i.e. "each customer must have Dir* fields always" then the choice is keep them in he same table and make Dir_ fiedls Required = Yes (NOT NULL). If you keep them in a separate table you cannot enforce rule "Each customer must have Dir_rep_ID, Dir_show_Email, dir_Show_Cell".

Two tables: If a customer may exists without Dir_ fields, then the separate table is the option. Why? If you have one table, tbCustomers with Dir fields not mandatory, the fields will have to accept NULL values when Dir_ filed(s) do not exist. Solution without NULLs is violates relational theory (per C.J. date) and it makes sense mathematically. I can explain that further if needed. With two tables, if a customer has data for (Dir_rep_ID, Dir_show_Email, dir_Show_Cell), the a record will exist in table tblDirectoryInfo. If there is no record foe some customer in tblDirectoryInfo, it means the customer has no data in (Dir_rep_ID, Dir_show_Email, dir_Show_Cell).

 

[Pat Hartman]

The data associated with Rep has nothing to do with company. Therefore, it ALWAYS requires its own table.

 

[stardustvega]

@Pat Hartman I'm sorry, I think I'm not explaining this clearly.

I understand a company can have multiple reps, so the relationship between TblCustomers and TblReps are separate tables, with a one-to-many relationship.

However, each company can only list ONE rep in the printed directory. Let's call this person their Directory Rep.

So, the goal here is that I must list which rep (from TblReps) is the single rep that they want listed in the directory. Does that make sense?

Let's say I have Sample Co. Sample Co has three reps listed in the the TblReps table. Let's say those reps have IDs 23, 24, and 26. They can have as many reps as they like but they may only choose ONE rep to be printed in the directory.

My question was, is it better to keep the Directory Rep's ID in the TblCustomers or to keep all the directory info in a separate table.

Based on the comments upstream, I see that everything that relies solely on the Customer's ID should stay in the same table, so what this would look like would be something like this:
TblCustomers:

Customer_ID	Customer_Name	Dir_Rep_ID
1	Sample Co	23
2	Lorem, Inc	43
3	Ipsum LLC	72

In the above example, for TblCustomers, Customer_ID is the primary key, and Dir_Rep_ID is a foreign key that refers to the primary key from the TblReps table.

(Edited for clearer language and less repetitive phrasing.)

 

[Pat Hartman]

Then all you need in the company table is the ID of the rep. That is what relationships are all about. You do NOT duplicate data. If a different company choses the same rep to be listed in their directory, then it becomes more obvious where the rep data belongs. Do NOT duplicate it.

If a company can have multiple reps - not what you said in the first post, then you have a m-m relationship and that requires a junction table. In that case the "directory" must be marked in the junction table row for that company/rep combination.

 

[stardustvega]

The confusion is because I said "There can only ever be one directory rep per company." E.g. a company can have many reps but they can have only one rep that's listed in the directory (the directory rep).

I realize now it was not clear what I meant by that a 'directory rep'. I used a term we use in my office without thinking about whether it would be meaningful outside that context.

At any rate, the responses from @ebs17 and @The_Doc_Man helped answer my question about best practices in terms of grouping the data, and I do understand what you said about not repeating data.

Thank you all for your help; I've marked the question as solved.

 

[Pat Hartman]

So, you actually have a many-many relationship?????? If so, the directory indicator belongs in the junction table, NOT in the company table at all.

 

[The_Doc_Man]

I disagree.

Let me clarify by adding one word.

You couldn't enforce mutual referential integrity with a one-to-one table.

You could "cheat" by having a one-to-many relationship that just happened to never have a "many" case - but might have a NULL case. However,what usually happens with Access and a 1/1 with RI enabled both ways is that whichever table you try doing an insert will give you an RI error because there is no corresponding record in the other RI member table. It's a form of deadly embrace. I've actually seen it (before I learned why it shouldn't be done).

 

[ebs17]

Mutual referential integrity cannot be set for any relationship type using the same keys.
I wonder why this is brought into play here.

A relationship itself and creative designs in tables are first of all different things and should not be sold as a bundle.

 

[The_Doc_Man]

Mutual referential integrity cannot be set for any relationship type using the same keys.

Ah, but once you split the table, presuming that they both still use a prime key, you are no longer using the same keys data-wise. Back in my early days when I was still a dripping wet newbie, I was unaware of the "laws" regarding relational integrity. Like the Bobby Fuller group, "I Fought the Law and the Law Won." Which is why I'm experienced.

You DO understand experienced, don't you? Experience is the ability to recognize your mistakes when you make them again.

 

[ebs17]

Each table uses its own key.
1:1 relationship:
The secondary table has a foreign key field unique by index. This field is related to the primary key of the primary table. Done, RI no problem.

The secondary table does not need its own primary key, and it does not need a key that is independent of the relationship.

Experience is the ability to recognize your mistakes when you make them again.

Correct. But experience also means not making some mistakes in the first place, or at least not repeating them.

 

[The_Doc_Man]

Jeez, EBS - lighten up. If you have seen my responses before, you would know that I toss a little bit of humor now and then. And you miss the point yet again. Splitting a monolithic table into two parts, having the same key for each but retaining a 1/1 relationship, introduces all sorts of errors (some of which I experienced when I was new to Access.) That is the point I was making to the OP.

 

[isladogs]

@The_Doc_Man
I wrote a reply to posts #5 & #15 earlier today but forgot to click Post Reply...so here is a slightly modified version of my original reply

As @ebs17 has rightly pointed out, referential integrity can certainly be set for 1:1 relationships including cascade update/delete

For example:

In fact, adding both cascades is particularly important for 1:1 relationships
It ensures that records can be added to / deleted from either table without any issues

All of the above is absolutely standard behaviour

I don't even understand what this phrase could possibly mean

You couldn't enforce mutual referential integrity with a one-to-one table.

By definition, referential integrity applies to both tables in the relationship

I have a lengthy 3-part article on this topic

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk