How should I handle a customer name being company or individual

[DeanWilliams]

I am creating a database where the customer can be either a company or an individual and I was wondering what the best way to approach this was. I would like to search by last names in the case of indivduals and by the first word in the case of a company. I am therefore not sure whether to have one or 2 fields (FirstName;LastName) or 1 field (CustomerName).

My original thought was to use 2 fields, & in the case of a company have LastName empty, but I can see this causing problems when searching by last names.

...Actually I have just had a thought: I could have a Yes/No field that would indicate whether the customer was a company or individual and the search could be structured accordingly using union queries or some other technique.

I am not sure that this is the best way, and if anyone has had to deal with this situation or knows a better method could you please let me know.

Thanks in advance.

Dean.

 

[JeremyNYC]

Dean,

Yeah, this is a sticky one. If at all possible, I would say, use two tables, tblCompany and tblPerson. If a customer is a person, make the CompanyName something like:
Person: PersonID
or
Person: FName LName
or something like that. I guess it's pretty clear that this is not something I've done before, so I'm not entirely sure how it would work out. But it seems to make sense, in that you'll most likely want to have a one-to-many relationship between companies and people, so you can track info about the people in companies. And it would be nice to be able to track people and companies in separate tables, so you don't have all sorts of blank fields for all of your records. There would clearly be a bit of overhead involved, as you'd have to figure out how to generate a personID while you're still creating a company record, or figure out how to skip the ompany form and create a person record in some process that would create a company record behind the scenes. But it sounds like an interesting thing to wrestle with.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[KornGeek]

My personal preference is to use one field (Customer Name), and then just format the customer name as Last, First when it is a person. It makes life easier if you only have one table design, rather than maintaining two tables.

However, if you need to track whether it is a person or a company for any other purpose, I would use Jeremy's suggestion of two tables. For instance, if you are tracking the people within the companies, then my suggestion will be inadquate.

 

[DeanWilliams]

My database is for a mobile phone company that covers insurance claims. They provide for companies, but I do not want to track people in these companies so I am more inclined to use one table.

I would rather use 2 fields for first & last names but unless anyone can see a way around it, I will go with KornGeek's suggestion of 1 CustomerName field (which is actually the current state of the DB).

I guess I could let the code in the data-entry form handle the formatting of people's names (i.e: Last, First).

Thanks guys & if anyone has any other suggestions...

Dean

 

[montrose]

Dean, You're 'sub-classing entities'. Here's an interesting example that may addrress your needs.

http://www.mvps.org/access/tables/tbl0013.htm

HTH, Montrose Learn what you can and share what you know.

 

[DeanWilliams]

Thanks Montrose,

That is very helpful.

Think I will go with that suggestion.

Cheers.

 

[montrose]

Dean, Glad you found it helpful. I think it's a good basic design. Best of luck, Montrose Learn what you can and share what you know.