Trouble understanding relationships 3

[utahjzz98]

Greetings,

I am fairly new at DB design and I am having difficulty grasping how to create relationships between my tables. Please see my tables below.

----------------------------------
|       CUSTOMER TABLE           |
+---------------|-------|--------+
Customer_ID	|INT	|PRIM Key|
Company_ID	|INT	|	 |
Contact_ID	|INT	|	 |
Product_ID	|INT	|	 |
Customer_Type	|CHAR	|	 |
+--------------------------------+

----------------------------------
|       COMPANY TABLE            |
+---------------|-------|--------+
Company_ID	|INT	|PRIM Key|
Name		|CHAR	|	 |
Address		|CHAR	|	 |
City		|CHAR	|	 |
State		|CHAR	|	 |
ZipCode		|CHAR	|	 |
+--------------------------------+

----------------------------------
|       EMPLOYEE TABLE           |
+---------------|-------|--------+
Employee_ID	|INT	|PRIM Key|
Name		|CHAR	|	 |
Department	|CHAR	|	 |
Email		|CHAR	|	 |
+--------------------------------+

----------------------------------
|       CONTACT TABLE            |
+---------------|-------|--------+
Contact_ID	|INT	|PRIM Key|
Name		|CHAR	|	 |
Phone		|CHAR	|	 |
Position	|CHAR	|	 |
Email		|CHAR	|	 |
+--------------------------------+

----------------------------------
|       TICKET TABLE             |
+---------------|-------|--------+
Ticket_ID	|INT	|PRIM Key|
Customer_ID	|INT	|	 |
Source		|CHAR	|	 |
Date_Assigned	|DATE	|	 |
Date_Completed	|DATE	|	 |
Assigned_To	|CHAR	|	 |
Status		|CHAR	|	 |
Notes		|CHAR	|	 |
Priority	|CHAR	|	 |
Product		|CHAR	|	 |
Description	|CHAR	|	 |
Sales_Person	|INT	|	 |
+--------------------------------+

What I was trying to accomplish is to have all customer information linked to the Customer table. For example, the Company, Employee, and Contact table would all link to the Customer table to make one large table. Then, I would link the Customer_ID field to the Ticket table and have all the data from the four "linked" tables. This just doesn't seem like it will work and I can't quite wrap my brain around the proper solution. Am I headed the right direction? Can somebody guide me in the right direction?

 

[Zathras]

This is what I can infer from the data given:

1. You have multiple companies with which you work.
2. Each company could have multiple customers.
3. Each customer has one and only one contact.
4. Each customer buys (sells?) one and only one product.
5. Customers have "types" but companies don't.
6. The same contact could be associated with multiple customers.
7. There are employees, but it is not clear how this table will be used. (Sales_Person?)
8. You have a "product ID" but no product table.
9. You have a Product as CHAR in the TICKET table. This should probably be a Product ID and when needed the Product (description) could come from a product table.

If any of those inferences are false, then you might have a problem.

Without knowing the subtle distinction between company and customer, that's about all I can say.

 

[utahjzz98]

Zathras,

Thank you for your reply. I'm sorry that I didn't clarify what I am attempting to do in my application. I work in a tech support department. I am attempting to create an application that will track our trouble tickets that are opened via email and phone.

1. You have multiple companies with which you work. We sell our product to multiple companies. Those companies are who I am attempting to represent.

2. Each company could have multiple customers. No, we deal with each company individually.

3. Each customer has one and only one contact. No, each company has multiple contacts.

4. Each customer buys (sells?) one and only one product. No, they can have multiple products.

5. Customers have "types" but companies don't. Correct.

6. The same contact could be associated with multiple customers. No.

7. There are employees, but it is not clear how this table will be used. (Sales_Person?) I was hoping to use the employees table to store both sales people and tech support people.

8. You have a "product ID" but no product table. I forgot to inlclude that table in my original post.

9. You have a Product as CHAR in the TICKET table. This should probably be a Product ID and when needed the Product (description) could come from a product table. Thank you that makes sense.

After answering those questions it appears I am not even close to accomplishing what I intended. I am confused. Maybe you can point me to an article or knowledge base on how to create relationships. I have attempted to read a few but they are pretty confusing.

 

[johnwm]

Go to:

http://www.alphabetcitydataworks.com/fundamentals.htm

and download the article by Paul Litwin. That will give you an good understanding of the ideas and practice behind relational databases.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[utahjzz98]

Thank you for your response. I will give that article a try.

 

[Zathras]

johnwm, I think it is an excellent article for beginners. If utahjzz98 won't give you a star (he never has in the past), I'll give you one for a very useful reference.

 

[utahjzz98]

Zathras,

Thanks for pointing that out. I don't post here much so I wasn't sure how that worked but I did give him a star for the great link. I also gave you a star for taking the time to respond to my original post. Thank you both for your time.

 

[johnwm]

Thanks guys! I have found it a great article

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[utahjzz98]

Ok,

I have read the article a few seperate times and tried to let everything sink in. I'm still a little foggy but I will post what I am have come up with and hopefully you can correct me if I didn't do something correctly.

PLATFORM (platform_id, type)
EMPLOYEE (employee_id, name, department, email_address)
EMPLOYEE_SALES (employee_id, company_id)
COMPANY (company_id, address, city, state, zipcode)
CONTACT (contact_id, name, phone_number, position, email_address)
COMPANY_CONTACT (company_id, contact_id)
CUSTOMER (customer_id, platform_id, company_id, contact_id, )

This is only the 1st half but I am trying to make sure that I have it correct before moving on. I also have a question about the many to many link table I created (company contact). There will be multiple companies and each company can have more than one contact. My question is for the Customer table where I have the contact_id and company_id. Which table should I link those to as the foreign key? Also, I have a table for Employee Sales which is used to track which sales person is responsible for that customer. So, I have attached the employee_id to that customer. However, I would like to be able to track help desk tickets in the future if the customer ever calls in and would have employee_id's for technical support and customer service. How would I seperate them?