references to other records

[windsorville]

I apologize if this has already been discussed in a thread, but I am new to php and sql and trying to follow along with some guides to learn them by creating a contact matrix for work. In this database, it will have a table for employees, a table for supervisors, a table for departments, teams, and divisions, and a table for tasks. I am primarily focused on the employee table for this question, and on the employee table it will have the employee’s first name, last name, their contact information (phone, fax, email) and also their supervisor’s name. Now the supervisor is also an employee with their own record in the database, and their employee record also includes their supervisor, and so on.

So the question is whether it is possible to create a column in the table which will be the name of the person’s supervisor along with a link to that supervisor’s contact information. I keep thinking about this in terms of html and the tag (something like John Smith ), but I don’t know whether it is possible to do this without doing a query in the database. Can a query be inserted into a column of a table to automatically bring you to another record “on click”? Any help with this would be greatly appreciated it.

P.S. - if it simply cannot be done, please let me know. I’d rather give up than to agonize over trying to figure this out. If you don’t know how to do this without spending large amounts of time, I don’t mind doing the research myself, I just don’t know what to search for. If there is name for it, let me know and I will gladly look it up and get learning!

[sebrenauld]

This can be done, but is not necessarily efficient. Let me elaborate.

The way you think of MySQL as a storage system is completely not how it works. MySQL itself holds one file per table. This file has the indices at the top (including primary key), followed by the raw data. Every time you force a lookup, MySQL searches through the index for what you want. If it cannot find it, it returns. If you are looking for non-indexed stuff, it will look through the entire table, which is costly.
Running two requests is actually not a big deal. What is a big deal is using crazy request terms or overly complicated requests. The entire aim of database structure is to allow the coder to optimize what people call a “query path”.

Let’s take your example. You have employees, who each have a supervisor. A supervisor is an employee, and therefore will also have a supervisor. We can use the concept of foreign keys to solve this in a trivial manner.

A foreign key is a reference from one field of a table to a field of another. In practice, it is a constraint but also works without explicitely defining the constraint. I won’t generate it - just outline the logic.
Your employee table will be as follows:

• id. PRIMARY KEY, integer, unsigned. Auto-incrementing
• name
• supervisor_id (unsigned integer. THIS NEEDS TO BE THE SAME TYPE AS id)
• As much data as you like

This is pretty much your entire system. Your supervisor’d ID is his employee ID. Simple.

Cast an index on supervisor_id - just normal indexing. You will therefore end up with your indices being:

• id PRIMARY
• supervisor_id INDEX
  This will allow you to do lightning-fast requests on either field. A good example of this is the following query, which you tried to do: getting the name of the supervisor

SELECT e.name AS employee_name, s.name AS supervisor_name FROM employees e LEFT JOIN employees s ON (s.id = e.supervisor_id)

This will print you the name of all your employees with the name of their supervisor. The request should be near-instant even on large amounts of data, as you are taking full advantage of integer indices.