[eluser]jedd[/eluser]
Ahh .. so you think of it as a
field - that necessarily contains one thing - rather than a
column, which obviously contains many things.
And no - I ain't afraid of no steenking ASses .. though they can be a bit more cumbersome to write, it's the exception (unless you're pulling ID's from multiple tables all the time) not the rule. More often than not I find that I'm selecting specific bits from a table, and I might JOIN or select WHERE based upon the contents of fields like 'id' and 'type', but it's rare that I actually select those bits of data (so I end up with less collisions than you might think, and consequently don't need that many AS or table aliases).
The single table for all persons approach .. it's something I was hitting last night, actually, with one of my own tables - and I'm about to post a plaintive request for help with the next step on that bumpy road. It's not beautiful when you first look at it, but it's quite valid and seems to work a treat. I rattled up some test data (below) to help sort a few things out in my own mind .. perhaps I've gone a bit overboard here.
Anyhoo, assuming a schema that includes this:
Code:
CREATE TABLE person (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
type CHAR,
first_name CHAR(99),
surname CHAR(99),
PRIMARY KEY (id)
);
CREATE TABLE guardian_student (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
guardian_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX (guardian_id),
INDEX (student_id)
);
And this test data:
Code:
INSERT INTO person (type, first_name, surname)
VALUES ("s", "Bart", "Simpson");
INSERT INTO person (type, first_name, surname)
VALUES ("s", "Lisa", "Simpson");
INSERT INTO person (type, first_name, surname)
VALUES ("s", "Maggie", "Simpson");
INSERT INTO person (type, first_name, surname)
VALUES ("g", "Marge", "Simpson");
INSERT INTO person (type, first_name, surname)
VALUES ("g", "Homer", "Simpson");
INSERT INTO guardian_student (guardian_id , student_id)
VALUES ( (SELECT id FROM person WHERE first_name="Homer") , (SELECT id FROM person WHERE first_name="Maggie") );
INSERT INTO guardian_student (guardian_id , student_id)
VALUES ( (SELECT id FROM person WHERE first_name="Homer") , (SELECT id FROM person WHERE first_name="Lisa") );
INSERT INTO guardian_student (guardian_id , student_id)
VALUES ( (SELECT id FROM person WHERE first_name="Homer") , (SELECT id FROM person WHERE first_name="Bart") );
INSERT INTO guardian_student (guardian_id , student_id)
VALUES ( (SELECT id FROM person WHERE first_name="Marge") , (SELECT id FROM person WHERE first_name="Maggie") );
INSERT INTO guardian_student (guardian_id , student_id)
VALUES ( (SELECT id FROM person WHERE first_name="Marge") , (SELECT id FROM person WHERE first_name="Lisa") );
INSERT INTO guardian_student (guardian_id , student_id)
VALUES ( (SELECT id FROM person WHERE first_name="Marge") , (SELECT id FROM person WHERE first_name="Bart") );
You can pull out a table to show this:
Code:
+--------------------+---------------------+
| student_first_name | guardian_first_name |
+--------------------+---------------------+
| Bart | Homer |
| Bart | Marge |
| Lisa | Homer |
| Lisa | Marge |
| Maggie | Homer |
| Maggie | Marge |
+--------------------+---------------------+
... with this command:
Code:
SELECT
person.first_name AS student_first_name,
person_ALIAS.first_name AS guardian_first_name
FROM
person
LEFT JOIN
guardian_student ON guardian_student.student_id=person.id
LEFT JOIN
person person_ALIAS ON person_ALIAS.id=guardian_student.guardian_id
WHERE person.type="s";
To clarify - I'm selecting all students (person type 's') and showing their first name, and the first name of each and every one of their guardians.
It's fairly straightforward to extrapolate that out to include phone numbers and addresses for both parties.
