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
Normalization ... Clients --> Companies vs. IndividualsProper normalization question regarding tables to store Project inform
Page 1 of 1
2 Replies - 1117 Views - Last Post: 30 January 2008 - 05:20 AM
#1
Normalization ... Clients --> Companies vs. Individuals
Posted 29 January 2008 - 09:12 PM
Replies To: Normalization ... Clients --> Companies vs. Individuals
#2
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
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
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

#3
Re: Normalization ... Clients --> Companies vs. Individuals
Posted 30 January 2008 - 05:20 AM
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:
Hope this helps.

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.
Page 1 of 1