[SQLDownUnder] Table Design

[Jake Ginnivan]

Hi all,

 

Was wondering if you could help me with my table design for a small CRM I have running at work, I don’t think it is the best way to do it but I don’t know how else I would do it.

 

I want to have 3 types of entries, Individuals, Companies and Contacts. Contacts are individuals that are entered under a company.

 

At the moment I have something like this.

 

Id,

Type (int 0 for company, 1 for individual, 2 for contact),

Display Name (what will be displayed in crm, either business name, or individual name depending on type, this is a computed column which is persistent)

Company,

Firstname

Lastname

 

And a few other things, email address/phone numbers are stored in related tables.

 

My problem is that when I query for the data, I exclude type 2, they are displayed below when a company is selected. But I want the company related to the contact to be displayed if that record matches the search terms.

 

What I want to be able to do is all entries of type 2 that are matched, add the company entry to the results, then at the end remove all duplicates, I can see this is a extremely inefficient way to do it (if possible), so if anyone can help me with the Table design I would greatly appreciate it.

 

Jake Ginnivan

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net