Handling company and private customers

[Tectu]

I have a table named tblCashbook which represents a cashbook (debit and credit transactions including the information which was the 2nd party of the transaction). The party can either be a company or a private person.

My question: Should I have two separate tables, one for private persons and one for companies or should I try having the two in one table? My motivation to go for separate tables is that the information to be stored is very differently: The company has a website URL, maybe a contact person(s), tax number(s) and so on. Private persons have maybe multiple phone numbers (Home, Mobile, ...).


However, the problem I see is the following: In the table tblCashbook I want to store the ID of the party. This would be the foreign key which is the primary key in either the private persons table or the companies table. However, when using AutoNumber the same ID occurs twice (both the private persons table and the companies table may have an ID 5). To prevent this I could use GUIDs but I heard that this is usually a bad thing to do.
When storing both persons and companies in the same table, how would I want to organize that?

Any advice is welcomed. I have zero experiences designing databases.

[Perceptus]

for the autonumber:
I would use a vba function to pre-seed the ID field with your own custom function. Then insert the data into the newly created record. You could generate the ID field with all sorts of information so that you could tell by looking at it how it is relevant to the data, while still keeping it pretty generic.

As for design, yes you could split it all down into a different design and have it relational and such. Having it designed correctly will save your sanity down the road when the design is in place.

[CJ_London]

I would go the single table route as it is much simpler and avoids issues over uniqueID as you have discovered. As a minimum, have in that table fields which are common to both companies and individuals. Perhaps also include a field to indicate type (person/company). Contact persons should be in a separate table anyway but have a family key back to the 'main' table. I would argue the same for things like phone and email - you may keep these in separate tables - or as I prefer a single table with two additional fields to indicate home/work etc. and another to indicate type (landline/mobile/email etc)

As to fields that are specific to company or person, for simplicity keep in the same main table and leave blank when not appropriate (you can hide them in your form design) or technically you would create separate tables with a PK field which is also the FK back to the main table (it would be type long, indexed, no duplicates, rather than autonumber). You would need a query which would pull up the data from both these tables (using a left join) when looking at all types of customer. Really depends on how many fields you are talking about, how complex it is all going to get.

With regards GUID's I've not heard they are a bad thing to use - providing they are appropriate for the task in hand (such as extremely bad ass large datasets or a requirement for synchronisation), however they would not address your issue anyway so not appropriate in this case.

[Tectu]

Thank you for your replies. I have now decided to have two fields in my tblCashbook table: One for the foreign key when it is a private customer and one for the foreign key when it is a company. I just have to ensure that always just one of the two is set while the other one stays empty.
Is that a somewhat sane solution?

[CJ_London]

Is that a somewhat sane solution?

Not the way I would do it, but you know your business better.

From your relationships, you will need to create the record in tblIndividual before you can create the record in tblContact which in turn needs to be created before you can create the record in tblCompany So I suspect you will have issues in adding records.

If you look at your tables you have many of the same fields in tblIndividual and tblCompany, and others that could be the same (e.g. phonenumber1 and phonenumber) - think about that and what I suggested re an address table

[Tectu]

From your relationships, you will need to create the record in tblIndividual before you can create the record in tblContact which in turn needs to be created before you can create the record in tblCompany So I suspect you will have issues in adding records.

Is there a sane way to solve this? At the end of this I would like to use forms for everything. Hence when adding a new company record I thought I would just put the fields there to add a new contact person which automatically creates a new record in the Individual table. I assume subforms would handle this or is this not possible? Would I explicitly need to create the records in individual forms first before I can add a new Company entry?

If you look at your tables you have many of the same fields in tblIndividual and tblCompany, and others that could be the same (e.g. phonenumber1 and phonenumber) - think about that and what I suggested re an address table

I understand what you mean. I will try creating a separate address table. Thanks!

[CJ_London]

Is there a sane way to solve this?

think of it this way - at the top of the data pile you have customers. customers may be individuals or companies - so far as I can see the only difference between the two at this point is that individuals have a 'client since' field and companies have a website (not sure about iscustomer etc). Also individuals have firstname, middlename, lastname whilst companies just have companyname

both types have addresses (one each) and cashbook entries

companies have contacts - do you need their personal addresses? I'm assuming not

contacts and individuals have contact information (phone numbers etc)

sketch these out on a piece of paper and see how they join.

- cashbook would link to customers
- addresses would link to customers
- contacts would link to customers (there just wouldn't be any for individuals)
- contact info links to both customers and contacts - because there is a good chance that a customerPK is the same as a contactPK you need an extra field to indicate whether the parent is a customer or a contact.

Note in your relationship window, you can drag a table onto it more than once - first time it will be tblContactInfo, second time it will be named tblContactInfo_1 - connect customers to one and contacts to the other

[Tectu]

Thank you for your reply. I have now brought it down to this: (Image below).
The only thing I have not done (yet?) is having a separate table for the address information. I am not sure what the gain is. I assume that I would still have to enforce a One-to-One relationship?

The reason I put the CompanyFK field into the tblBusinessContact table is in order to have multiple contacts in the same company (eg. an engineer, a sales man, ...).

[CJ_London]

if you only have one address per customer then you might as well leave it in the individual/company tables.

I still don't think you should have separate records for individual and company but I've been unable to persuade you

good luck with your project

[Tectu]

I plan to have more than one address field in the future (eg. DeliveryAddress, BillingAddress, ContactAddress, ...).
Indeed I still have separate tables for private clients and companies... I am sorry :-/ I just want to avoid having a huge table where always 50% of the fields are empty.

I got one step further and I added the separate address table now.

[CJ_London]

in the address table you shouldn't need recipientname or department because they are detailed elsewhere