one to many design

  • Thread starter Thread starter inungh
  • Start date Start date
I

inungh

I have a table design base on business requirements that have
folliwng:

Parent table

Key1

Child Table

Key1
Key2
Key3

Does it make sense in the database design?
If it does not make sense in the database design, should I review the
business rules again?
Do I need have a middle table which has Key1 and Key2?
It seems MS Access does not give me one to many link to have one key
in parent and 3 keys in the child table.

Your information and help is great appreciated,
 
I have a table design base on business requirements that have
folliwng:

Parent table

Key1

Child Table

Key1
Key2
Key3

Does it make sense in the database design?
If it does not make sense in the database design, should I review the
business rules again?
Do I need have a middle table which has Key1 and Key2?
It seems MS Access does not give me one to many link to have one key
in parent and 3 keys in the child table.

Your information and help is great appreciated,
Click to expand...

Does your child table have three *FIELDS*? or three *RECORDS*?

There's an old saying: "fields are expensive, records are cheap". If you have
three fields your table design is probably incorrect. Could you please explain
a bit more about the real-life situation? What kind of Entities do the two
tables represent? Does each Parent record have many Child records? Does each
Child record potentially relate to multiple (three? up to three? maybe more?)
Parent records?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Does your child table have three *FIELDS*? or three *RECORDS*?

There's an old saying: "fields are expensive, records are cheap". If you have
three fields your table design is probably incorrect. Could you please explain
a bit more about the real-life situation? What kind of Entities do the two
tables represent? Does each Parent record have many Child records? Does each
Child record potentially relate to multiple (three? up to three? maybe more?)
Parent records?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for the message,
Child table has 3 keys fields, but only one key field in the parent
table.
Yes, there are multi records in child table relate to parent table.
This is what I guess that I am not in the right direction. It will too
long to expalin the business rules here.

In short, parent table is tbluser which key is UserID
Child table is a User Template to fill the activity when user enter.
The problem is the activity (ActivityID) has one child called element
(ElementID)
If I want to have the user template table to have element level then
my user template table must be

UserID,
ActivityID and ElementID, but without middle table which has UserID
and ActivityId,

Thanks again for helping,
 
Thanks for the message,
Child table has 3 keys fields, but only one key field in the parent
table.
Click to expand...

What's the relationship? Which field is it linked to?
Yes, there are multi records in child table relate to parent table.
This is what I guess that I am not in the right direction. It will too
long to expalin the business rules here.
Click to expand...

Well... then it will take even longer for me to explain the answer, since I
don't really understand the question!
In short, parent table is tbluser which key is UserID
Child table is a User Template to fill the activity when user enter.
The problem is the activity (ActivityID) has one child called element
(ElementID)
Click to expand...

How are Users, Activites and Elements related?
If I want to have the user template table to have element level then
my user template table must be
Click to expand...

What is a "template table"? That doesn't appear to be an Access term.
UserID,
ActivityID and ElementID, but without middle table which has UserID
and ActivityId,
Click to expand...

That's not a question, and I'm not at all sure what you're trying to say!

Let's keep at this... again, if you could describe (in general terms, you
needn't post your entire business rule set) what real life Entities each table
represents, and how those entities are related to one another in the real
world, we should be able to help.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
What's the relationship? Which field is it linked to?


Well... then it will take even longer for me to explain the answer, sinceI
don't really understand the question!


How are Users, Activites and Elements related?


What is a "template table"? That doesn't appear to be an Access term.


That's not a question, and I'm not at all sure what you're trying to say!

Let's keep at this... again, if you could describe (in general terms, you
needn't post your entire business rule set) what real life Entities each table
represents, and how those entities are related to one another in the real
world, we should be able to help.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

I have user, activity, element and user template four tables.

user table has following fields
user id, user name, user code, user rights.....

actitivy table has following fields
activity id, activty code, activity name, actiivty description,

Element table has following fields
Element id, element code, element name, element description,

and I am design user template to let user fill the information from
user template table base on user creates the combination of activity
and element the relationship between activity and element is many to
many.

I need have a composite key for user template table which is user id,
activity id and element id to get right template base on user's
activity and element selection.

It seems that I need use the middle table of activity and element
table for their many to many relationship and user template table to
link with user table and activity element table.

Thanks again for helping,
 
It seems that I need use the middle table of activity and element
table for their many to many relationship and user template table to
link with user table and activity element table.
Click to expand...

That is correct. A many to many relationship does indeed need an additional
table, related one to many to each parent table. If each User can be involved
in many Activities, and each Activity may involve many Users, you need an
intermediate table with one-to-many relationships to each.

I still don't understand how you're using the term "template". To me a
template is a table (or form, or other object) which can be used to create a
new table. That does not appear to be the case here.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
That is correct. A many to many relationship does indeed need an additional
table, related one to many to each parent table. If each User can be involved
in many Activities, and each Activity may involve many Users, you need an
intermediate table with one-to-many relationships to each.

I still don't understand how you're using the term "template". To me a
template is a table (or form, or other object) which can be used to create a
new table. That does not appear to be the case here.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

template is the name of the table I am design.

thanks again,
 
Back
Top