one to many design

[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,

 

[John W. Vinson]

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,

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

 

[inungh]

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 -

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,

 

[John W. Vinson]

Thanks for the message,
Child table has 3 keys fields, but only one key field in the parent
table.

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.

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)

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

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,

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

 

[inungh]

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

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,

 

[John W. Vinson]

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.

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

 

[inungh]

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

template is the name of the table I am design.

thanks again,

 

[tina]

this is an old post, but just in case you're still working on it:

as i understand it, one activity may have many elements, and one element may
be included in many activities - as you posted previously, that is a
many-to-many relationship. a template is the assignment of one or more
specific elements to a specific activity - the "union" or "join" table
between activities and elements, forming the -many side of a one-to-many
relationship with each of those two tables. and one user may create many
templates, but each template is created by only one user - a one-to-many
relationship.

if the above is correct, try the following tables/relationships structure,
as

tblUsers
UserID (primary key)
UserName
UserCode
UserRights

tblActivities
ActivityID (pk)
ActivityCode
ActivityName
ActivityDescription

tblElements
ElementID (pk)
ElementCode
ElementName
ElementDescription

tblActivityElements
UserID (foreign key)
ActivityID (fk)
ElementID (fk)
<you could assign a surrogate primary key here, or use the three foreign key
fields as a combination primary key.>

relationships would be
tblUsers.UserID 1:N tblActivityElements.UserID
tblActivities.ActivityID 1:N tblActivityElements.ActivityID
tblElements.ElementID 1:N tblActivityElements.ElementID

though if you are tracking additional data that describes a specific
*activity* of a specific *user*, you may want two tables here instead of
one, as

tblUserActivities
UserActivityID (pk)
UserID (fk)
ActivityID (fk)
<other fields that describe a specific activity as related to a specific
user - there should be no fields describing elements, in this table.>

tblUserActivityElements
UserActivityID (fk)
ElementID (fk)
<you could assign a surrogate primary key here, or use the two foreign key
fields as a combination primary key.>

relationships for the two tables above would be
tblUsers.UserID 1:N tblUserActivities.UserID
tblActivities.ActivityID 1:N tblUserActivities.ActivityID
tblUserActivities.UserActivityID 1:N
tblUserActivityElements.UserActivityID
tblElements.ElementID 1:N tblUserActivityElements.ElementID

hth
tina

What's the relationship? Which field is it linked to?


Well... then it will take even longer for me to explain the answer, since
I
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

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,