Database design problem

We have to design a database for UserInfo. We already have a user
table, and contains userid. There are three user type(ex. student,
teacher,admin), they have their own tables. i don't know how to add the
personal infomation field. One way is putting some common field into
the user table, the other is laying the infomation of subtype into
their own tables.Which is better?Is personal info the same for all types? is it present in all types? If yes,
feel free to add it to the user table.
MC
"Readon Shaw" <xydarcher@.163.com> wrote in message
news:1132886720.966817.144210@.o13g2000cwo.googlegroups.com...
> We have to design a database for UserInfo. We already have a user
> table, and contains userid. There are three user type(ex. student,
> teacher,admin), they have their own tables. i don't know how to add the
> personal infomation field. One way is putting some common field into
> the user table, the other is laying the infomation of subtype into
> their own tables.Which is better?
>|||yes, but what is the advantages of that?
MC wrote:
> Is personal info the same for all types? is it present in all types? If ye
s,
> feel free to add it to the user table.
> MC
> "Readon Shaw" <xydarcher@.163.com> wrote in message
> news:1132886720.966817.144210@.o13g2000cwo.googlegroups.com...|||Readon Shaw wrote:
> yes, but what is the advantages of that?
The same advantage as that of having a user table at all - to help you
to apply constraints against it and therefore ensure the integrity of
your data. For example, if you have a name or email address that's
common to all types of user you'll perhaps want to add a constraint
that the address or name be unique across all users. That's much easier
to do if they are all in one table.
In general, the fewer places in which one attribute can appear, the
fewer the opportunities there are for anomalies and incorrect data.
David Portas
SQL Server MVP
--|||Phew, thanks David, I was trying to come up with a short answer :).
In addition let me point out that the entity in this design is User, and
that 'additional' tables might be called 'derivations'. Since the
information you're adding to design is based on entity and not on
derivation, you should apply it there. And only there.
Am I making sense here?
MC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132916473.316709.85460@.f14g2000cwb.googlegroups.com...
> Readon Shaw wrote:
> The same advantage as that of having a user table at all - to help you
> to apply constraints against it and therefore ensure the integrity of
> your data. For example, if you have a name or email address that's
> common to all types of user you'll perhaps want to add a constraint
> that the address or name be unique across all users. That's much easier
> to do if they are all in one table.
> In general, the fewer places in which one attribute can appear, the
> fewer the opportunities there are for anomalies and incorrect data.
> --
> David Portas
> SQL Server MVP
> --
>|||MC wrote:
> Phew, thanks David, I was trying to come up with a short answer :).
> In addition let me point out that the entity in this design is User, and
> that 'additional' tables might be called 'derivations'. Since the
> information you're adding to design is based on entity and not on
> derivation, you should apply it there. And only there.
> Am I making sense here?
>
> MC
>
Intuitively that makes sense, yes. More formally, this problem is the
subject of a design rule that Chris Date calls the Principle of
Orthogonal Design, which is used to prevent certain types of redundancy
and anomaly. Roughly this says that integrity constraints should
prevent data being recorded in more than one table if there is some
non-loss decomposition of the same data into fewer tables. That doesn't
absolutely preclude the user name (for example) appearing in both the
Teacher and Student tables but it does require that there should be
some constraint to prevent the same person appearing in both. For
practical reasons it is therefore more efficient to implement one
column and one constraint in one table rather than some more complex
constraints across several tables.
See the following article for more discussion of this.
http://www.dbdebunk.com/page/page/622331.htm
David Portas
SQL Server MVP
--|||Just to be certain, i didnt really try to explain the logic to you David,
but to the original poster :).
MC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132923609.030176.20790@.z14g2000cwz.googlegroups.com...
> MC wrote:
> Intuitively that makes sense, yes. More formally, this problem is the
> subject of a design rule that Chris Date calls the Principle of
> Orthogonal Design, which is used to prevent certain types of redundancy
> and anomaly. Roughly this says that integrity constraints should
> prevent data being recorded in more than one table if there is some
> non-loss decomposition of the same data into fewer tables. That doesn't
> absolutely preclude the user name (for example) appearing in both the
> Teacher and Student tables but it does require that there should be
> some constraint to prevent the same person appearing in both. For
> practical reasons it is therefore more efficient to implement one
> column and one constraint in one table rather than some more complex
> constraints across several tables.
> See the following article for more discussion of this.
> http://www.dbdebunk.com/page/page/622331.htm
> --
> David Portas
> SQL Server MVP
> --
>