Best Practices: Whether to keep two tables separate or share single table? (1 Viewer)

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
I have to redesign the database as things has changed. I've already built a table holding records of Clients along with their address information and some attributes that relates to Clients.

Now, I need to create a table for our Business contacts. This will also need to store address info and then some attributes relating to businesses.

The problem is I'm not sure if I want to take address out of Client table and have Client and Business table link to Address table to follow the rule of not having any repeating data or simply allow Business table to store address.

What would be the best thing to do here?

Thanks.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
Clients and businesses are both entities with which you do business. They could probably be stored in the same table. If you elect to use separate tables don't forget to enforce RI.
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
I've elected to make it separate, reasoning that since I have to store physical and mailing address of our client, it'd be easier to have a Address table that is linked to Business and Client tables.

Thanks for your suggestion. :)
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
I've elected to make it separate, reasoning that since I have to store physical and mailing address of our client, it'd be easier to have a Address table that is linked to Business and Client tables.

Thanks for your suggestion. :)
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
Ok- now I'm a bit confused.

If I try to have a table shared by two tables (e.g. Address table referenced by Business table and Client table), what would everyone usuall call the foreign key?

Seem to me if I used ClientID (as per old design), it would cause more problems if BusinessID had same value as a ClientID.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
If you want to share the address table between two entity tables, the address table has to be the one side table. That means that the AddressID goes into the business table and also the client table. If you want to have multiple addresses for an entity or to share addresses among entities, you MUST have a single entity so you're back where we started with an entity table and possibly two 1-1 tables (client and business) related to entity. Then address is related to entity rather than to client and business.
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
Ok, so I tried the suggestion, but realized it wasn't that simple. The thing is that Clients has a number of tables which comes with their correspending lookup tables. They are related by ClientID. Those tables will not relate to Business at all, and I would imagine there may be eventually tables that relates only to Business in future. The address table is now related to entity table, allowing both to have many addresses.

So, I went and redesigned the Client table so the primary key is 1-1 to Entity table, while retaining ClientID as non duplicating autonumber so I don't have to change all other tables that references ClientID. Is this a good idea? (I tried making EntityID and ClientID a composite key but seems to create more problem so I set EntityID as only key while allowing ClientID to be indexed).

Also, the roadblock I may have with this is if I were to create a new Client record, I would have to first create Entity Record for this new record to relate. What would be the best way to do this transparently as possible, given that I already have a form for creating a new Client record that opens on a NotInList event?

I hope I'm not needlessly complicating my design.

Thanks
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
EntityID should be the primary key in all three tables. In the entity table, it is an autonumber. In the other two tables, it is a long integer and a foreign key to the entity table. This defines the 1-1 relationships.

With this structure, there is no need for separate business and client IDs. I think this is confusing you.

The forms that are used to create business and client entries should be based on queries that join the entity table to either the business or client tables. Make sure that RI is properly defined between the entity table and its 1-1 child tables. You should also select CascadeDelete.

As long as at least one data field in each table is populated, Access will handle the insert of both tables from a single form.
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
So by your logic, I should just go and change all ClientID in the child tables (currently a many side table related to Client table) that relates to Client record to EntityID, even though those child tables may never ever relate to Business entries?
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
Okay, another question.

I have two tables-

tblGuest
*attributes
CountyID
CategoryID
EthnicityID

tblClient
*attributes
CountyID
CategoryID
EthnicityID

Both references the same lookup tables. Looks like repeating data to me, so I thought about taking them out and putting in oh say, tblDemographic and have tblGuest and tblClient relate to that table 1-1.

However, I'm afraid that I'm now "mirco-tabling" (is that a word? ;) ) and creating more problems-

How guest may relate to clients-

1) Not all guests are clients.

2) Not all clients are guests.

3) Of those guests who does come in for client services, they may not necessarily the clients themselves (e.g. they are a parent/guardian/whatever) and therefore will have a different demographics.

4) We do not keep any personal information about Guests beyond the simple demographics. We do keep personal information about Clients.

Now, what I'm worried is if I went and took out the Demographic into its own table, this would warrant me changing GuestID and ClientID to EntityID in order to allow both tables to refer to tblDemographics, which would complicate several queries I've already made and ultimately hurt my database's performance.

I hope someone can advise me whether this is a situation where repeating data is okay or perhaps a better way to manage the data without having to make a zillion joins on my queries to have a user-friendly form and reports. :)

Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
Put the demographic fields in the Entity table since they apply to both clients and guests.

The Entity table needs a field that identifies whether the entity is a client or a guest.

Your application performance is not going to suffer if you join to the entity table. The primary key and foreign keys are all indexed which makes joins extremely efficient.
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
But what about situation where Guest can be Client or vice versa? How would I set it up to allow multiple identities for single entity? Junction table seems to create more problem...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
We started out talking about clients and business contacts. How did we switch to clients and guests? You have started several threads to talk about this problem and I have to admit to total confusion.

Does a guest become a client? Does he then remain a client or may he go back to being a guest? I think you have confused a "transaction type" with an entity type. In one of your earlier posts didn't you talk about visits to your clinic or whatever it is and the same person could be a guest or a client depending on something? Is is that an entity may have some transactions as a client and some as a guest?

People are people and that's the way it is. They belong in the same table. If there are only small variences in the types of data you need to store, the best solution is to live with the null values in some fields. You can add some code behind your forms to make certain fields required or hidden depending on the status of the person.

These people then have some interaction with you and that is called a transaction. In the transaction table, you store information related to that visit and that visit only.

Bottom line - an entity does not need multiple identies. That would only confuse your reporting. Please don't start any new threads on this topic and please give us a summary of your current understanding of your application.
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
I apologize for the confusion.

I'm trying my best to keep within lines for keeping data normalized. However, I'm trying to solve several problems at once, apparently.

The database is there to keep track of who comes in to our office. It may include guests, clients, and business contacts.

The difficulty was in comprehending how an entity works with us over the time, and we have different rules that we are obliged to follow for different entities. At the present, the tables are arranged in a snowflake fashion.

With guests, no information is kept about them, and if they come to our office, that's two records, even if they are the same person.

With clients and business contacts, we have one and only record. They have their own corresponding tables for their visits and particulars.

Guests may become a client, but will be continued to be counted as guest if they don't come in for our services (e.g. they walk in to get some information, then leaves- that would be a guest incident). Same is true of business contacts.

The thing that got me confused was the fact that services we provide to guests may overlap with services for clients, so it seemed logical to me to allow clients and guests to be related.

Now in writing that, I can see that you're right- I'm confusing entities with transaction, and what I have been calling guests is more of a transaction. I'll go back and look over how I have treated guests in the database and fix it.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
One thing that is not quite "normalized" that might help with making your application more consistant is to add a dummy "Guest" person and whenever a Guest comes in for services, you add a transaction for the Guest person. This will allow Clients and Guests to share the same services and allow you to maintain referential integrity and create many-to-many relationships - People/Services and not have to worry that you don't have the same level of information for guests that you have for clients.
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
I thought that creating a dummy (or blank) record is a bad thing?

When I wrote my last post, I took another look at my relationships and decided to give it another try. I hope this will work better?

tblPerson (this is master table)
*attributes relating to person such as names and demographics

tblAddress (1-M to tblPerson)
*attributes, with lookup tables

tblContact (1-M to tblPerson)
*attributes, with lookup tables

tblClient (1-1 to tblPerson)
*attributes, with lookup tables

tblBusiness (1-1 to tblPerson)
*attributes, with lookup tables

tblVisit (1-M to tblPerson)
Services (FK to ServiceType
*other attributes
*has other lookup tables referencing Visit)

tblService (1-M to tblVisit)
ID
ServiceClass (lookup table)
ServiceType

With the structure above, every person has a "visit", and tblVisit requires that there be a Service stored, which will also tell me whether this person is a Guest (you can see there is no need for a table holding Guest), Client or whatever.

Since all clients and business clients may have same services as guest, it's no problem to use both ServiceClass "guest" and "client", since I can also use PersonID and VisitID to tell me what kind of service a person got on a particular visit.

On the forms, whenever a user enters a visit incident, Access transparently saves the type of services, depending on which tabs user selects (though on one tab for tracking attendees, it is a combobox selectable by user to differinate among various classes we provide.

I hope this is normalized design, and not missing anything. That was quite a paradigm shift for me when you pointed out that I was confusing transactions with people.

Thanks!
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
I thought I had my table design down until I received word from my uppers that there may be a new contract, which may cover some services we already offer under another contract. In other words, I need to track which services is funded by which (and which can be more than one) contracts. I think they want to make my life a living hell. :D

The present structure is designed to be person-centric, and allows person to be "neutral" so the record can be added to different aspects of our services (e.g. guests, attendees, clients, and maybe some more) to allow for multiple classification for single record, and visit table holds information on what kind services (which has their own classification).

Before I go in and add more tables to allow for multiple (!) classification of funding from which contract for services, I thought I better ask again and ensure I have my design right before I create more problems. Here's my relationship windows.

So-

Question #1: Does the present relationship show correct support for multiple classification of a person among various services that we provide? (e.g. a person can be any combination of guest, attendee, client, or whatever without violating the normalization)


Question #2
Will inserting a junction between the related table and the tblFundingSources and tblServiceClass, which are derived from present contract, allow for multiple classification of how services are funded without violating normalization *without* needlessly complicating the overall design?

I hope that was clear enough. If there's questions, let me know and I'll do my best to clarify. TIA.
 
Last edited:

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
Would love it if anyone can give me an indication whether I'm on right track.

Thanks. :)
 

Banana

split with a cherry atop.
Local time
Today, 07:20
Joined
Sep 1, 2005
Messages
6,318
Bumping in hope that someone will at least assert my nagging little voice in my head. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
You have some suspicious m-m relationships. Usually "code" tables are related 1-m. For example tblClientAccom.. and tblClientCommu... Do those records really occur many times for each client?
 

Users who are viewing this thread

Top Bottom