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

[Curt Sampson <cjs@example.com>]

On 2008-01-11 14:38 +0900 (Fri), Micheal Cooper wrote:

> 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.

This sort of thing, when taken to excess, is a standard novice mistake
with relational database design. In its most extreme form, you end
up with a couple of tables storing everything in the database in
attribute-value form.

That's not to say it might not be the right thing to do in this
circumstance, but keep in mind that it tends to make a big mess of your
data. Just think about the sort of constraints you'd like to put on your
database, and that will probably lead you to the places where you might
use this and the places where you might not.

> (2) an Update_Attrs() function which analyzes student data to give and
> remove attributes according to the results of certain checks (i.e. if
> (current GPA < 2.5) student.give_attr(attributes.assign("probation"),
> date.today()))

That's just data duplication that's going to lead to inconsistencies. If
the rule is, "the student is on probation at all times when his GPA <
2.5, you don't need to store whether or not he's on probation, since you
can simply directly check the GPA for the date in question and find out.
To store separately that he's on probation or not can only allow you to
let him have a GPA < 2.5 and yet not be on probation (according to that
flag), or let him have a GPA >= 2.5 and have him be on probation.

Remember, a database is really an inference engine: you give it a set
of facts, and then ask it questions about other facts that are not
(directly) stated. So there should be no way of asking a particular
question that would give a different answer from any other.

As an implementation point, views can be very handy for giving easy
access to calculated data.

> In textbook database design, if you are told to add the ability to
> track scholarship students, you need a scholarship table, and if you
> are asked to track computer lab assistants, you make a campus_jobs
> tables.

I've never seen a textbook that states this particular thing explicitly.
I think you're probably drawing unwarranted inferences. :-)

cjs
-- 
Curt Sampson       <cjs@example.com>        +81 90 7737 2974   
Mobile sites and software consulting: http://www.starling-software.com