Re: [tlug] database design idea... what do you think?

["emiddleton@example.com" <emiddleton@example.com>]

Micheal Cooper wrote:
> College database application, with administration users who frequently
> request new things added to the database.
>
> What do you think of the following idea for a way to introduce ad-hoc
> attributes for users:
>
> Make table "attributes" in a many-to-many relationship with table
> students. Include from_date and to_date to indicate the dates the
> student acquired and lost this attribute. Also include field operator
> to record the user who entered the data and change_date to record the
> actual timestamp of the data entry. Attributes about the student that
> need to be tracked (like advanced placement, scholarship, probation,
> etc.) are each a record in the table.

I generally take the approach of working out the object model and then
mapping it to the database using standard OR mapping patterns (i.e.
ActiveRecord, Foreign Key Mapping, Association Table Mapping, Single
Table Inheritance[1]).  Creating the schema this way forces you to think
about whether something is really an attribute of a student or a
relationship between a student and some other object.  It also helps
avoid schema hacking.

Edward

1. Patterns of Enterprise Application Architecture by Martin Fowler