Normalization ... Clients --> Companies vs. Individuals

Proper normalization question regarding tables to store Project inform

Page 1 of 1

2 Replies - 770 Views - Last Post: 30 January 2008 - 05:20 AM Rate Topic: -----

#1 Nolan  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 52
  • Joined: 12-September 07

Normalization ... Clients --> Companies vs. Individuals

Post icon  Posted 29 January 2008 - 09:12 PM

Hi all.
I am 'helping' with a friend's database for his company.
It has been developed over the past few years by a receptionist with moderate Access experience.

There is a table that stores "Project" information. A Project is identified as a 'job' that is 'billable'.

The table structure includes a Client_ID, and a Contact_ID.
Clients are either Companies or individuals, and Contacts are the 'other' person to be contacted.

The current Client table has a field called "LastName/CompanyName"

I think the idea here is to define Client_Types (Individual vs. Company) in a new table, but I am wondering about how to structure the tables beyond that.

Here's the lowdown:
1. Projects have Clients
2. Clients are either individuals or companies
3. Clients have primary contacts AND secondary contacts

How should the tables be normalized?

Tbl_ClientTypes
--ClientType_ID
--ClientType

Tbl_Contacts
--Contact_ID
--ContactFname
etc...

Tbl_Projects
--Project_ID
--Client_ID

Is this the way to do it?

Thanks in advance...

Nolo

Is This A Good Question/Topic? 0
  • +

Replies To: Normalization ... Clients --> Companies vs. Individuals

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1639
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Normalization ... Clients --> Companies vs. Individuals

Posted 30 January 2008 - 12:38 AM

It looks like you're on the right track there, in my opinion anyways, but make sure in your clients table to add the ClientType_ID for each client. I would also move to SQL Server as well, since you can have a limited number of concurrent connections whereas with SQL Server you can have as many concurrent connections as the server can handle, and as long as the queries are written correctly, such as adding NOLOCK after each table you're including in the query, such as


SELECT * FROM TableName (NOLOCK)



This will prevent any blocking issues that can occur with concurrent connections, or when 2 people are accessing the same table at the same time.

If you dont have access to SQL Server and you're stuck using Access, just make sure your queries are written efficiently to help prevent any blocking issues that can occur.

I know I got way off track, but as far as your original question, thats more than likely the way I would go to normalize the data, and make it easier to write efficient queries.

P.S: Don't even use SELECT * FROM TableName unless you need all the columns in the table, doing this just pulls back data that is ultimately discarded and uses unnecessary resources :)
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5796
  • View blog
  • Posts: 12,631
  • Joined: 16-October 07

Re: Normalization ... Clients --> Companies vs. Individuals

Posted 30 January 2008 - 05:20 AM

You left out Tbl_Client :P

While this is one approach, you'll have a problem; foreign keys. Sometimes you're willing to loose them, sometimes you're not. Here's how I'd keep them.

Tbl_Contact
--Contact_ID
--Client_ID (FK to Tbl_Client)
--ContactFname
--IsPrimaryContact

Can your contacts belong to more than one client? Probably not. If they can, that's another senario, we're going with this one for now. You'll want a rule so that only one IsPrimaryContact=true can exist for each Client_ID.

Tbl_Client
--Client_ID
--Company_ID (allow NULL) (FK to Tbl_Company)

You'll want to require that every client has at least one primary contact.

Your client query might look like this:
select a.Client_ID, IsNull(c.CompanyName, b.LastName) as ClientName
	from Tbl_Client a
		inner join Tbl_Contact b
			on a.Client_ID=b.Client_ID
				and b.IsPrimaryContact=1
		left outer join Tbl_Company c 
			on c.Company_ID=c.Company_ID



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1