RE: [SQLDownUnder] Table Design

[Greg Low]

Hi Jake,

 

Couple of thoughts that might help.

 

It sounds like you're really trying to model several things into the one table. Whenever you find yourself adding a column that says "what type of thing this is", you need to stop and wonder whether it should be a separate table. In your case, it's hard to know how many columns are common to all contacts but rather than having everyone as a contact and then trying to subdivide the contacts, you might consider storing the appropriate columns for each type of contact in a separate table. Then when you need a list of contacts, you just grab them out of all the required tables using a union statement.

 

Also, consider how you would "compute" a display name from a Last Name and First Name. By "last name" are you thinking of the person's surname or are you really talking about their "last" name? Would what you have in mind work for people who's family names come first and their given names are last?

 

HTH,

 

Greg

 

Dr Greg Low
Readify - Senior Consultant

M:	+61 419 201 410
	Office Communicator

 

---

From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Jake Ginnivan
Sent: Saturday, 2 September 2006 6:26 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] Table Design

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 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