Relational structure logics vs. customer' logics

[TJ53]

Hello,

I just started working on a real state database that somebody else started (yai ...). It has the following tables (simplified):

- Buildings

- Apartments

- Contacts

Now the customer needs to add two new entities, related to "buildings":

- Broker companies

- Sales agents (1 broker company can have multiple sales agents)

So I created two separate tables, one for "broker companies", and another one for "sales agents", thinking that in the future they might ask, for instance, for specific broker's or sales agents' reports (e.g., broker fees by period, etc.). (I also created a join table so they can have a sales agents portal for each building)

Now: the client is asking to have ALL CONTACTS in the same table, meaning having also broker companies and sales agents as part of the contacts table (they say that in a "normal address book" you would have all your contacts in the same A to Z list, regardless of being friends, family, doormen, brokers, co-workers, or sales agents)

So I can think of three options:

1) say to the client "no, you can't have everything in the same table", which doesn't seem to work ...

2) doing some scripting so every time a record is created either in the "broker companies" or "sales agents" is added to the "contacts" table (that involves some additional scripting when records are updated ...)

3) Adding two foreign keys to the "contacts" table (kf_broker_company and kf_sales_agent) so records are actually created in the "contacts" table and share some fields (name, tlf, email), and still have two separate tables for "brokers" and "agents" attributes.

Can anyone think of a 4th option? what would you do in this case?

Thanks in advance!

[bcooney]

I like option 3, which is very much the Graham Method . However, I do find it hard to be comfortable with Broker Companies in Contacts.

[TJ53]

Thanks a lot bcooney, I´ll definitely look into the Graham Method.

[David McQueen]

You are hitting a classic CRM problem - are the contacts company centered or individually centered.

He is telling you he has no priority and wants them all equally accessible. It is not a moot request. I have been asked to go into existing CRM solutions that were one way but the organizational reporting and day to day operations required required working the other way. It was very frustrating for them and totally hampered their work flow. Also, once the architecture is established, it is very hard to alter.

A thought:

1. Give him his single table.

2. Have a field to differentiate whether contacts are brokers, agents or other (client, customer, supplier.....I do not know how general he wants to be). Then simple searches can be very effective and he gets his "Phone book".

3. Have a broker key so that agents can be related to brokers via self relationships. Then you can show all sales agents for a particular broker in a portal and show brokerage affiliation in an agent record via a related field all within the same table.

4. I believe that if it is structured correctly you wll be able to pull reports for any building showing either sales agent or brokerage affiliation.

Basically what you are doing is hiding one table within the other.

[jamesducker]

1. Give him his single table.

2. Have a field to differentiate whether contacts are brokers, agents or other (client, customer, supplier.....I do not know how general he wants to be). Then simple searches can be very effective and he gets his "Phone book".

3. Have a broker key so that agents can be related to brokers via self relationships. Then you can show all sales agents for a particular broker in a portal and show brokerage affiliation in an agent record via a related field all within the same table.

4. I believe that if it is structured correctly you wll be able to pull reports for any building showing either sales agent or brokerage affiliation.

Couldn't agree more. Single table every time. J

[TJ53]

Thank you very much for the responses. The suggestion about creating self relationships is interesting, but here is the thing: I don't feel very comfortable about having, for instance, broker companies and sales agents in the same table. I'm not sure about having a table with two or more different entities, and therefore attributes that apply only to some groups of records.

That is, let's say that they ask me to be able to input the gender of the sales agents. I would add the "gender" field to that single table, but that field is not relevant to the group of records that are "broker companies".

Following bcooney's suggestion, I had a look at the "Graham Method" post, that is very interesting and made me think of the following architecture:

What is the common attribute / field to every contact? all have a NAME field.

So I created "CONTACTS" table with the following fields:

CONTACTS

__kpContact

_kfBrokerCompany

_kfSalesAgent

_kfDoorman

Name

BROKERCOMPANIES

__kpBrokerCompany

YearEstablished // example of field relevant only to this table

SALESAGENTS

__kpSalesAgent

_kfBrokerCompany

LastName

Gender

DOORMEN

__kpDoorman

LastName

Schedule

TELNUMBERS

__kpTelNumber

_kfContact

TelNumber

Type

----------

Relationships are 1 to 1 as follows (simplified ...):

BROKERCOMPANIES > CONTACTS

SALESAGENTS > CONTACTS

DOORMEN > CONTACTS

This way records are created from the appropriate table layout (Brokers, Agents, Doormen) and as soon as the name is entered, a record in the CONTACTS table is also created, since the NAME field actually belongs to the contacts table.

Please have a look at the attached example. Any feedback would be greatly appreciated!

Contacts.fp7.zip

[jamesducker]

I would definitely suggest three tables:

ORGANISATIONS

- uniqueOrgID

- orgName

- type (broker/salesagent/doorman*/supplier/customer/other)

- mainPhone

- mainFax

- website

ADDRESSES

- uniqueAddressID

- parentOrgID

- line1

- line2

- line3

- line4Town

- line5County

- line6Postcode

PEOPLE

- uniquePersonID

- parentOrgID

- parentAddressID

- firstname

- surname

- gender

- email

- directPhone

This basic structure will manage perfectly fine in nearly all situations. The title of the thread says it all, really: it is so, so easy to be blinded by the apparent "oddness" of a customer's setup. In reality though it is very very seldom necessary to deviate from the above, however complex the business. We have the same basic CRM structure above for all our clients' systems and it works just fine. From what I know so far, I can't see why it wouldn't work in your situation too.

*I'm assuming 'doorman' is an industry term for some kind of organisation, rather than meaning 'man on the door' :-)

[TJ53]

Thanks a lot for your time James, I'll give it a thought.

P.S.: I was going to explain what a "doorman" is, but I'm sure Wikipedia can do a better job, here you have the link:

http://en.wikipedia.org/wiki/Doorman_(profession)