Welcome Guest, Not a member yet? Register   Sign In
If data exists in the field print, else don't
#31

[eluser]jedd[/eluser]
Actually, I'm regretting now not pushing to have the distinction between student and guardian somewhat blurred - or rather, made in a different way to having two similar tables - as it's just going to get messier having all people having addresses, emails, phone numbers and so on - but having to double up on each of those connecting tables.

I'll run it through the background processing part of my brain, but my thought now is table to combine student / parent:
person: [ id , type , fname , lname ]

type could be ENUM or just a CHAR and obviously denotes parent or student.
#32

[eluser]slowgary[/eluser]
It probably would make more sense to just have a 'participants' table and use a type=ENUM('guardian', 'student'). It might also make querying more difficult. I can't picture how you'd do what your currently doing with a few joins unless you use multiple queries (yuck).

Also, I have to disagree with you Jedd on your recommendation of removing prefixes. I've seen your arguments before on prefixes and singular names, and while it does make a lot of sense to me to see code like this:
Code:
student.id
guardian.phone
as opposed to this:
Code:
students.student_id
guardians.guardian_phone
I still vote for the latter only because of the annoyance of having a bunch of "AS guardian_phone" clauses in my join queries. Especially in this scenario where there will be a lot of joined fields that will have the same name.

As far as having a student_phones table, you really only need it if the relationship between students and phones is one to many (or if you actually care about storing more than 1 phone per person). You could keep the phones table and just store the phone_id in the students table. This would still allow people to share phone numbers.

Also zebake, your view code is just going to need some logic to test for these scenarios. I'm curious though, will you not be displaying the student's actual address? If not, how do you know which address is the student's permanent one (in the event that there are multiple guardians)? Would you even store additional addresses? Like if a student lived with one parent but you store the other parent for an emergency contact, would you also need that second parent's address? Basically, I don't see in your examples an easy way to determine where to send the student's report card.

I think the easiest way to do the view is to use a few "temp" variables to store the previous loop's value, then test against it in the loop and branch your echos with a few if/elses.
#33

[eluser]jedd[/eluser]
[quote author="slowgary" date="1246651257"]Also, I have to disagree with you Jedd on your recommendation of removing prefixes.[/quote]

That's okay .. I know that one day you'll come round to my way of thinking ... Wink

I do try to point out that there are two (or more) approaches to these things, even though I tend to evangelise somewhat.

My suggestion above was primarily to encourage zebeke to pick one approach and stick to it, not mix and match within a database.

Quote:
Code:
students.student_id
guardians.guardian_phone

Hmm .. are you really advocating plural table names, but singular versions of those table names as the prefix for each column?

I think even with a brain the size of a small planet, I'd have trouble remembering the rules for that taxonomy ...


Quote:As far as having a student_phones table, you really only need it if the relationship between students and phones is one to many

I think that was the original problem - there was email1 and email2, and phone1 and phone2 in the early design of the tables - presumably in response to some people wanting two contact methods. I'd understand this, especially with mobile/work/home phone numbers (I wouldn't call them cell numbers (not just because I'm not North American, and I call them mobile phones, not cell phones) but just plain phones). Of course, I don't know the specs - maybe mobile numbers are all they're allowed to store, and/or no one has home or work phone numbers in the USA anymore. It's a weird old place, so anything's possible I guess.

But yes, I'm working on the assumption that one person can have >1 contact number. And I don't care if a contact number happens to get stored in two locations in the phone table (this seems easier than managing a later change of number for someone who shares a phone.id reference to a once-common phone number).
#34

[eluser]slowgary[/eluser]
I hope to not take this thread into the much trotted direction of table naming conventions, but how do you deal with conflicting field names with your method? Are you using a bunch of ASses (that doesn't look right ;-P)?

On the contrary, my brain is the size of a small cheerio, and yet this naming convention just makes sense. The table is a collection of students, while the field stores the data for one individual instance of the table. Basically, theres one students table (a table which contains many students) and many student_id fields (a field which contains one student_id).
#35

[eluser]TheFuzzy0ne[/eluser]
EDIT: Deleted - because I'm a muppet.
#36

[eluser]slowgary[/eluser]
I usually have the wrong end of the stick (something smells like pooh). Using a table name prefix for foreign keys only makes sense, but what would you do for zebakes case where he has a students table and a guardians table, and each have fields like 'fname', 'lname'. Or would you argue as mentioned that students and guardians should both be in one table? It makes sense to combine them, I would just have a much harder time I think getting the query right, looking for results like this:
Code:
student_id | student_lname | student_fname | guardian_lname | guardian_fname | guardian_phone
And this will still probably need a bunch of ASses (pooh-stick) to rename the fields.
#37

[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.
#38

[eluser]slowgary[/eluser]
That makes sense. You win ;-P
#39

[eluser]zebake[/eluser]
Haha, just when I think I'm making progress...

I think I should explain the purpose of this project a little bit more. This student directory is not for general school office purposes. This directory will replace a paper directory that is given to each parent. It is a small primary school, with no more than about 200 students. The main purpose of this directory is for parents to be able to contact other parents for birthday parties, event planning, fundraising, etc...as well as students to be able to contact other students for homework and project discussion.

Once it is complete, it will be integrated with some form of membership/authorization script so that the information inside is not open to the public.

Ideally we would also like to have the ability for parents to edit their own contact information.

On the topic of addresses, many students (my son included), do have two households. In my case, in particular, my son needs both his mother's address/contact info listed, as well as mine.

On the phones/cell topic, we use one phone number as the "main", most predominantly displayed number. This is the number that will display next to the student's name, and will generally be a home phone number. The cell phone numbers are optional, and will only be displayed for each parent who so choses to list theirs. We will not handle student cell phones and we don't need work phone numbers.

I think this is a bit more of a casual directory, if that makes sense.

I'm heading out to pick my son up and get an oil change, so I'll be brainstorming this some more and checking this thread from my mobile Wink

Thanks again guys!
#40

[eluser]garymardell[/eluser]
Quote:checking this thread from my mobile

Just don't do it when your driving. Tongue

I think everything you need is in this thread, including database schemas and even sample code. Read the comments and you will see what to ignore (or who to ignore), if you have any more questions feel free to ask. Try writing some code and we can help you debug it or suggest better ways maybe.




Theme © iAndrew 2016 - Forum software by © MyBB