Best Practices: Whether to keep two tables separate or share single table?

[Banana]

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.

 

[Banana]

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]

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]

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.

 

[Banana]

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

 

[Banana]

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]

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!

 

[Banana]

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...

 

[Banana]

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.

 

[Banana]

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]

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.

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.

 

[Banana]

Would love it if anyone can give me an indication whether I'm on right track.

Thanks.

 

[Banana]

Bumping in hope that someone will at least assert my nagging little voice in my head.

 

[Banana]

Actually those are lookup tables; however client can have multiple selections. On the form, a multi-select listbox is used with those tables as rowsource.

 

[Banana]

Great; good to know that I'm not quite hopeless.