Please Check My Progess - Table Diagram

Databases
1

Hey guys, I have just started learning (teaching myself) PHP and MySQL a couple days ago and so far I think I have a grasp on simple database / table relationships, but I just want to make sure that I am on track with my current project. Originally I thought this would be fairly simple, (and maybe to some of you it is) but it turns out that my project will be more complex then originally expected.

What I am trying to do is build a web based ticket system for my customers. I want it to manage both tickets and allow me to easily make invoices. Now before you ask why I am not using one of the many CRM applications available, understand that I have not found one that can meet all my needs. They are either lacking in features and customization, or poorly designed. Other applications that may work, are just simply way too expensive, so I have resorted to building my own.

Anyways, from my understanding, the best way to approach a project like this, is to first draw up a diagram mapping out the tables and their relationships to each other. I do not know how to show this to you guys other then to provide a link to the actual PDF that I printed from google docs spreadsheet, so I hope that is acceptable.

Edit:

Working link in post 9

It is by no means complete, but I just want to make sure I am going in the right direction. Also, if you have some input or advice, please let me know.

Thanks,

Dean

2

Hey, Im sorry to bump this, but I could really use some advice or confirmation on my progress.

Thanks.

3

sorry, can’t help you, man, i can’t read PDFs right now

4

Awesome, how about the rest of it? Am I on the right track?

5

yeppir, that’s how i’d do it :slight_smile:

6

Ok, so continue to us ID as the primary key, and then link the the primary key from that table as the foreign key in the table associated with it? The foreign keys would then be named something like Customer_ID and Contact_ID, but of course link back to the actual ID of the customer and contact table.

That is how I have it set up as of now, is that correct?

7

not to me it don’t :slight_smile:

when i see a column called id, i assume that it’s the primary key of that table

when i see a column called customer_id, i assume it’s a foreign key, and it relates to the id column in the customer table

if you call both of them customer_id, then you cannot easily look at a table and tell whether customer_id is the pk or not

8

I was not able to share my table yesterday due to an issue with google docs, but I am able too now, here is the link if that helps.

Would you mind changing the link on the first post to this one. Thanks.

9

I have already made the “many to many” relationship table for contacts and customers, as well as for a few of the other tables (you have to scroll to the right to see them) but it does make sense to rename the ID columns to the specific ID like customer_ID, especially sense that is the value that is going into the “many to many” relationship tables.

I have been watching and reading a few tutorials and in ever case the simple ID was alway used unless referring to another table. I wasn’t sure if there was a specific reason for that.

If you see anything else, let me know. I do not want to start actually making the tables tillI get this straight.

Thanks,

McKay

10

I would suggest you rename ID columns you have in each table to more specific customer_id, ticket_Id and so.
you are missing link between customer and contact tables seens you need to have many to many relationship you need another table CustomerXContact
comething like CustXCont_Id - unique key, Customer_Id foreign key, Contact_Id foreign key