Table design question

Topic

New Reply

Hi,

The database I’m creating will contain clients that are either an individual or a company. Most of the fields between these two ‘clients’ will be the same. However when creating an individual the individual will have a ‘first name’ and ‘surname’ field, whereas the company will just have a ‘company name’ field. Also when selecting an individual the user should be able to select what type of individual this is from a drop down list, and with the company they should be able to input the business number.

Is it better to have one ‘client’ table and have another table which links to this to say whether it is an individual or company? Or is it better to have two different tables? If I did decide to have one table, do I just lock or unlock different fields depending on whether the user is adding an individual or company? Also would I just use the ‘first name’ field to hold the company name?

Hope this makes sense!

Cheers,

Reply | Quote

Replies

Jason,

Table design a thorny question! From your description I think I’d have a single table since most likely you’ll have a contact person even if the client is a company. That way one of the people types could be “Contact”

Of course you could have an unlinked check box on your form which would indicate Person or Company and then hide or show fields to fill in based on that checkbox. You could even store that value and use it when the data is redisplayed.

You will of course have a clientno field of some sort that will be your primary key.

The possibilities are endless and depending on what your data set looks like will have to determine your final design. I hope these thoughts are of some help.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I agree with what RetiredGeek has said.
It is almost certain that you want a single table.

If you separated the data into two tables, you would find yourself writing union queries for lots of purposes where you want the various clients listed in a single list.

Also would I just use the ‘first name’ field to hold the company name?

I would be inclined to use the Lastname (or Surname ) field rather than Firstname. If I compiled a sorted list, the Individuals would normally be sorted on Surname.

Reply | Quote

Dear Jason,

Just a few thoughts to add to this discussion and perhaps clarify things. Whilst it may be “easier” to have one table what you need to consider is if the data that you want to collect for People is substantially different or Organisations? And also that People will be related to one (or possibly more, depending on what you are modelling) Organisation and one Organisation will relate to one or more Persons. This relationship can be solved in a number of ways:

[*]you can have a single table and a “free text” field with the person’s company but with no relationship to an organisation record so you will need to type it every time for each person and it means you have no single source of data for the organisation. So obviously not good unless you are sure you will never refer to a company more than once.
[*]You can have a single table (lets call it CONTACT) that doubles as a Person table and an Organisation table, this will mean that you will need to link the table to itself to maintain the person to organisation one to many relationship, this can be confusing since in practical terms it will mean that queries will have to have two instances of Contact one to represent the person and one to represent the organisation he is related to. In addition there is the issue of wanting to store different data – some is the same such as phone numbers and addresses but some will be entirely different.
[*] You have two tables and name them clearly – perhaps PERSON and ORGANISATION, if you find you have some similar data such as addresses you could split them out into another table but for most people this is unnecessary. This method will enable you to see clearly what you are dealing with when creating queries, reports and vba, and it will allow you to clearly define the unique properties of each. You can easily creat an amalgamated “Contact” view if you want something like a phone list.

Personally I would always opt for the latter solution it is clear and flexible. In the end it is personal choice.

Good luck…………………….. liz

Reply | Quote

Hi Jason,

I am in agreement with the members who suggested that you stick to a single table. It does sound like you want to keep track of ‘Clients’ which would best be kept in one table. Having designed several systems with a ‘Clients’ table myself, I would suggest that you have FirstName, LastName, and CompanyName fields as well as a ClientType code field. ‘C’ and ‘I’ code values would serve well, in this instance. The field names I have used here are placeholders for the purpose of this discussion. Of course you should use field names and client type codes of your own choosing.

You can always combine these fields for reporting and other purposes into a single field using a conditional expression in a query. In other words, an individual client, ‘John Smith’ stored in the LastName and FirstName fields and a Company client, “ABC Widgets, Inc.” stored in the CompanyName field can be made to appear in a single field, say “OrganizationName” for example (again, you would choose the actual field name, based upon your preference) in a query designed to do this. It is very easy, but I would be getting ahead of myself going into that much detail at this point. I just wanted you to be aware of this ability, in case you weren’t already.

There is always more than one way to design systems. I don’t claim that my ‘solution’ is the only correct one. Based upon my experience, it will work, however.

I hope this helps …

Charles.

Reply | Quote