How to code with more than one table?

Anon

I am creating a simple troube-ticket database in MySQL and decided to try to normalize it after reading the normalization article on this site.

So far, I have 3 tables:

faculty

id
fname
lname
room
phone
email
request

request

id
type
date/time
status (of request)
performed by (staff member)
requested by (faculty member)

staff

id
fname
lname
email
action (performed on request)

My question is, how do I put it all together? I created databases with one table before, but never multiple tables. Let's say a staff member performs an action on a certain request that was requested by a certain faculty member. How does one code all of this so the tables all work together?

Thanks

Anon

Well I'm glad to see that you are moving forward with database's, single table database are nothing more than flat files with an SQL interface.

I'm not sure that I understand your problem, but from what you have I'll try to put something together.

First off you will have a list of all of your faculity members in the faculty table. When one of them enters a trouble ticket you will just put there unique id into the requested by table. This way when a staff member looks at the trouble ticket you can query the faculty table and get the information on the submitter of the ticket. This lets you keep only 1 copy of redundant data, and referance it with your unique id's. Now when the staff member does something to the trouble ticket then you will put his id on the ticket, so that when the facuilty member comes back to check on it you can pull up the staff members information.

You will probably want to have another table called status that holds all of your status' and then just put the ID into the ticket table.

Also you might want to have a notes table that will allow the staff or faculty member can write up a description of the problem, or the solution. Then you'd link these notes to the ticket with what is called a cross-referance table.

notes

id
submitted_by
submitted_type - either staff, or faculty so you know where to look for the above id.
note

tickets_x_notes

id
ticket_id
note_id

Using this you can link any number of notes to a ticket. Ofcourse this is a simplified table structure, you'd probably want to add thinks like date/time...

Anyway the main reason to normalize is so that you can minimize the amount of data that you are storing, and give yourself access to that data when it is needed through a speedy method (unique id's with indexes).

I hope this helps, let me know if you need anymore information.

Anon

Thanks Rob,

My problem is figuring out how to write the tables in MySQL then trying to call the tables using PHP. Do I need to use joins and all that? How does one table know that the other exists? Like if need to call information across a wide array of tables, how do I combine all that onto a single webpage. I've found plenty of sample code for single tables, but can't find any for a database with multiple tables.

Thanks

Anon

Well you are getting into some sticky areas here. First off your tables don't need to know about each other, and unless you are using a database that supports "foreign keys" (MySQL doesn't, and PostgreSQL does it weird). But I doubt that you are. You as the programmer are supposed to know how your tables are supposed to ineract and bulid your program to do just that.

Combining all of your multiple data sets on 1 web page is easy. Just make multiple queries. I see many comments on this list about building hugh complex queries to get data from multiple tables. Unless your database is dog slow then you aren't going to incure too much overhead making multiple queries. It's also much easier to wrap your head around. I live by the motto KISS (Keep It Simple Stupid).

Query 1: Select ticket information from the ticket table.

Query 2: Using the requested_by id value returned from Query 1, to query the faculty table to get the faculty members information.

Query 3: Using the performed_by id value returned from Query 1, to query the staff table to get the staff members information.

Store all of the data from each query into an array or something, and then once you have all of the data then display it.