Best way to store two 1:N relations in database

[torrud]

Hi,

I want to know the best way of storing two 1:N relations in a database. I have three entities a "student", a "nonstudent" and a "pass". So i have three tables "student", "nonstudent" and "pass", but every student or nonstudent can have more than one pass. What is the best way to store that kind of information?
At the moment I have created additional two relation tables "student_pass" and "nonstudent_pass", but I think that is a little bit oversized, because every pass can only have one owner and relation tables are for M:N relations, or not?

Can someone give me a hint?
Torrud

[erickwidya]

hm..is student and non student have exactly same attribute? if it is, why don't u put it at one table and give flag that make it different ex : 'S' for student, 'N' for non..but if u need it different to make it easier to maintain then that's alright

...but every student or nonstudent can have more than one pass. What is the best waywell..u have it already..at student table u get StudentID for the PK..and at 'pass' table u get StudentID and Password as PK..from that u get 1 to M relationship

...because every pass can only have one owner and relation tables are for M:N relations, or not? not M to M, it more suite with 1 to M relation

it's only my opinion ok..

hope can help

[Krzemo]

Answer first to this questions:

*What is the structure for "student" and "nonstudent" and how it is different (how erickwidya already sugest U)?
*What is the planned (aproximately) size of those 3 tables?
*How fast they will be growing up?
*What kind of questions (SELECTs) will be puted against database?
*Is it more transactional or more reporting database?

It will help chooseing best structure for U.

Best regards,
Krzemo.

[torrud]

At first the table sudent and nonstudent do not have the same structure. Otherwise I used one table as erickwidya said. The size of the table should not being to much, because the program should be used on a workstation with an MSDE database system. They will growing up slowly, maybe 100 entries per month, maybe less.
I have only a few select statements which return only complete rows from database. Therefore I have implemented loading on demand, so I will only ask for the passes if I really need it.
I hope that are the informations you need for a hint.

[Krzemo]

I hope that are the informations you need for a hint.
Maybe, but since U don't provide structure of tables it can be not optimal ;) .

Than: My tip is to create one pass table with 2 nullable fields refferencing student/nonstudent eg:


CREATE TABLE Pass
(
...
,IdStudent INT NULL REFFERENCES Student(IdStudent)
,IdNonStudent INT NULL REFFERENCES NonStudent(IdNonStudent)
)


Add a check constraint that ensure that allways only one ID is null at a time.
Thats all folks :)

But in your case it really doesn't metter (small footprint, low bandwich database), and your solution is as good as my tip. I prefer to have it in one table (if this is small one) since it lower count of objects to manage.

Best regards,
Krzemo.

[jp140768]

I work on a database here, where the designer took the OO approach to the limit.

In your example he would have had a person table - this would be the primary table, from here he would have a student and non student table. There would be one entry in the person table, and then one in either the student or non student tables. In my case, this would also expand into teaching staff etc. There would also be a pass table.

HTH

[erickwidya]

My tip is to create one pass table with 2 nullable fields refferencing student/nonstudent Krzemo so all tables structure needed will be like?

In your example he would have had a person table - this would be the primary table, from here he would have a student and non student table. There would be one entry in the person table, and then one in either the student or non student tables jp140768 like i ask Krzemo..can u post the table structure?

PS : sorry if i'm not understand about this one since my english not good and i can understand better with example

thx :)

[Krzemo]

Krzemo so all tables structure needed will be like? ... like i ask Krzemo..can u post the table structure?

That's the problem: I don't know the structure of tables - it is known only by torrud.
And he said that this tables cannot be merged and that he would preffer 1 table for pass table.... and my tip was derrived from that .

Best regards,
Krzemo.

[erickwidya]

That's the problem: I don't know the structure of tables - it is known only by torrud. not the real structure..i mean if u in that condition, how u design it?

[Krzemo]

if u in that condition, how u design it?Hmm ...

Well, I will tend to create:
1) table Person
2) table pass with FK to table person
3) Table Student with FK to table person and unique key on that FK

Why?
1) I think there have to be some common data (student and nonstudent) which is related to personal info (First Name, Second name ... etc.).
2) One can have password disregarding of the fact that he is/or not a student. Sometimes someone that was a student can become nonstudent (can be unexpectedly :D ) and there is no need to change password in that situation. So password table is related to person table (not to student/nonstudent).That way u can enter multiple passwords for person.
But here a new question arrive: What is the reason to heve multiple passwords for 1 person - but that's another story :rolleyes: .
3) I believe that any additional data is related to student only, because if someone is a nonstudent than he is "person" only. So no need for nonstudent table. So student table is related to person table since it holds additional attributes of "person". To improve data integrity I would create unique key constraint on column PersonID (FK) - this way user cannot add 2 entries to "student" table. Of course U can also reverse relation from "Person" to "student" table (FK from "person" to "student") - but it will depend of particular needs.

Best regards,
Krzemo.

[erickwidya]

thx Krzemo


Well, I will tend to create:
1) table Person
2) table pass with FK to table person
3) Table Student with FK to table person and unique key on that FKthen it would be something like this?
tblPerson = @PersonId, PersonName, ...
tblPasswod = @PersonID, @Password, ...
tblStudent = @StudentID, PersonID (unique), StudentName, ...

so whenever non-student want to sign, it only need the 'tblPerson' and 'tblPassword' only..and for Student require additional info at 'tblStudent'? it sure make sense now :blush:

hm..i think i need to learn about RDBMS again! :D
thx

[Krzemo]

then it would be something like this?
tblPerson = @PersonId, PersonName, ...
tblPasswod = @PersonID, @Password, ...
tblStudent = @StudentID, PersonID (unique), StudentName, ...
Thre is no need for StudentID (PersonID will be enough).

Best regards,
Krzemo.
:wave:
PS: What is the meaning of "@" in your notation?

[erickwidya]

thx Krzemo

PS: What is the meaning of "@" in your notation? it's notation for Primary Key..it can go with underline, @ or both.

regards, :wave: