[Nolan]

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

[PsychoCoder]

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

[baavgai]

You left out Tbl_Client

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.