"Readon Shaw" <xydarc***@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?
>

> 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" <xydarc***@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?
> >

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1132916473.316709.85460@f14g2000cwb.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
> --
>

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1132923609.030176.20790@z14g2000cwz.googlegroups.com...
> 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
> --
>