Should I use a one-to-one relationship

[Seth Schrock]

I'm in the middle of planning a database that will track both students, prospective students and alumni. Obviously there is a lot of fields that would be the same between them, but more information would be gathered for students than for prospective students and then a few more fields for alumni. I know that I would have them all in the same table, but my question is how to handle the extra fields for for each type of contact. Do I have a basic contacts table with the fields that are duplicate and then have another table for the fields that only students would have and create a one-to-one relationship between the tables, or should have all the fields in one table and just hide the fields that don't apply?

[NeoPa]

Either can work Seth, but unless the numbers of fields are really unwieldy I'd stick with the simpler approach of including all the fields in a single table and simply using only the ones that are relevant.

For most situations though, both can work perfectly well.

[Seth Schrock]

Well, at the moment I'm not sure how many fields there will be because I'm having to normalize the database. Currently they have Child1 through Child7 names and birthdays so that will eliminate several of the fields just because there won't be related records in other tables. Right now I would guess between 10 and 20 extra fields, but I could be wrong.

[NeoPa]

So, they'll fit in a single table and you can choose to, or not to, use them depending on the circumstances. Unused fields take up very little space in a record.