one record from multiple tables

[presario]

I am designing a contact management application. I have one contact who has many companies, many phone numbers, many emails and many urls. I designed database in this way:-

 

1.) There is one base table tbl_contacts. Its fields are:-

contact_id, contact_name, contact_designation, type

 

2.) There is one separate table for separate information to store like tbl_companies, tbl_phones, tbl_emails

tbl_companies - contact_id, co_id, co_name, default

tbl_phones      - contact_id, phone_id, phone, default

tbl_emails        - contact_id, email_id, email, default

 

Am I in right direction?

[kickstart]

Hi

 

I would have a table of contacts, and a table of contact info. With contact info being

 

Id

ContactId

CommunicationType ie, phone, compaby name, email, but probably as an ID of a communication type from a table of communication types

CommunicationDetail ie, phone number, company name, email address.

Default

 

All the best

 

Keith

[presario]

thanks for your quick response. I want to keep record of as many phone numbers/emails/faxes/urls/ as my contact would have. With your scheme, I could only store one phone number/email/fax/url against each contact? whereas in my situation, my one contact has many companies, phone numbers, faxes, emails, urls and I want to track them all

[kickstart]

Hi

 

No reason you couldn't have multiple phone numbers. If you want them in order then just add a sequence / importance number to the table (have a unique key for contact, contact type and sequence).

 

Advantage of this is that when you add new types of contact (eg, say you want to add an MSN contact) you can easily add them without the need to add futher tables. It becomes a simple data change rather than a system change.

 

All the best

 

Keith

[presario]

I am sorry I am not getting you :-(

 

What I decided was to use table joins to fetch all records relevant to a particular contact_id but in your suggested way, would not I have redundant information?

 

I want to store each and every information of my contact. Number of phones/emails/urls/faxes he has. How we find him? Is he a prospect or customer or friend or an employee? When he was added in database? who referred him? how many prospect he has referred to us? how many times have to approached him and by which medium? Among all, there would be some default valuables which will be displayed at first and when we'll take details information of a contact, it would display all the information.

 

I am attached database table print for you to review. Take your time to suggest me something.

 

Thanks for your time

 

[attachment deleted by admin]

[SpankMarvin]

It seems like your original method and the suggested other approaches are all as valid as each other, and more dependent on how you feel most comfortable querying your data once it's being populated.

 

The first response in this thread is how I would approach it. You have a contact table, and that should contain all the unique info for which there will be only one value. Then, having a relational table that houses all other contact types means that, as long as you can define the types, you can query the data easily enough. When displaying a contact, you would query the contact info table, adding a clause that would filter by type as well as by contact id. Convert the results to an array and you have your list of, e.g. email address belonging to that contact.

 

Seems like a clean enough approach for such an application...

 

J

[presario]

Thanks for the info. I'll revise my database structure and will discuss further. As I'll incorporate project, accounts, employees, lead management in this same application, I think It is much better to spend good amount of time in designing database? What do you say?

Anyone willing to discuss this project or willing to suggest me throughout whole phase of development? Someone, I can communicate on IMs or PM?

[gizmola]

Thanks for the info. I'll revise my database structure and will discuss further. As I'll incorporate project, accounts, employees, lead management in this same application, I think It is much better to spend good amount of time in designing database? What do you say?

Anyone willing to discuss this project or willing to suggest me throughout whole phase of development? Someone, I can communicate on IMs or PM?

 

In my experience, the database design is never more than a small fraction of the amount of time it takes to develope the application.  If you understand the requirements sufficiently you should be able to get the design mostly right in a short amount of time.  I do however find that using a database design tool to be very helpful.  For mysql I happen to use dezign for databases, although that has gotten to be rather pricey over the years.  It is still inexpensive compared to some of the other commercial tools out there for doing database design.

 

 

[kickstart]

What I decided was to use table joins to fetch all records relevant to a particular contact_id but in your suggested way, would not I have redundant information?

 

To an extent, but virtually impossible to stop that with any solution beyond just doing extra selects within loops (which is hideously inefficient).

 

If you JOIN all the tables together you will get the common info duplicated, but this is far more efficient than having multiple selects.

 

To avoid this what you can do is use GROUP_CONCAT to build up a contacts field (containing all the contact info) for each row. However might not be that convenient in php (and there are issues with max field length).

 

All the best

 

Keith

[presario]

keith, have you reviewed attached database? I'll modify database and then show you again. There is another thing about contact which I want to keep record is communication history.

anyway, let me modify database. Then, I'll show you!

Thanks for your time!