Determin relationship

[Frank Situmorang]

Hello:


Can anybody tell me the best website explaining the table relationship? Coz
it is not so clear to me:

1. How can we determine whichone is to be the mother and which one is to be
a child in the relation ship
2. Is it alwasy that One to Many relationship that One is Monther and many
is child?
4. Should the mother only use primary key as the linking fiels?

Thanks in advance for any idea provided.

Frank

 

[FredFred]

Frank,

That's a pretty broad question. I'm sure someone can recommend a good site
or book to start learning the mechanics of linking tables. But your
question does have a bit of a "cart before the horse" aspect.

But underlying it all is deciding what information you want to store, then
deciding the best way to store it in relationship database tables, and then
deal with the mechanics of setting them up and linking them.

 

[Jeff Boyce]

Frank

I start with the data, not with the tables.

If I have, for example, an Order Fulfillment application, I have Orders,
Customers, Products and "Items Ordered".

But that still doesn't tell me quite enough about the underlying business
rules that apply. Can I have more than one customer on an order, or is my
"rule" one Order, one Customer (I'll go with this latter rule).

So which is the 'parent' (the "ONE") and which is the 'child' (the "MANY")?

I'm hoping for repeat business, so my assumption is that one Customer can
have many Orders (e.g., one-to-many). The primary key I use for Customer
will have to show up as a foreign key value in Order.

I'm hoping to sell a lot, so each Order can have many corresponding "Item
Ordered" records (again, one-to-many). The primary key in Order needs to
show as foreign key in "Item Ordered".

I'm using "Item Ordered" to show one Product per "Item Ordered" record ...
but I hope to sell many of the same Product, so one Product can show up in
many "Item Ordered" records (one-to-many). The same primary/foreign key
relationship exists.

Does that help?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Frank Situmorang]

Thanks very much Jeff, your illustration does help me very much.

 

[Tony Toews [MVP]]

If I have, for example, an Order Fulfillment application, I have Orders,
Customers, Products and "Items Ordered".

Nice description!

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Ken Evans]

Hi Frank,

Principles
The principles of "table relationships" come from original work on the
"Relational Model of Data" defined by the late Dr E F Codd in 1969.

A version of his paper was published in 1970 in the Communications of the
ACM with the title "A relational model of data for large shared data banks"
You can read this paper at
http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf


Parent and Child
Table structures reflect "facts" .
For example, "each Order contains many Items"
The words that I have capitalised map to tables so this fact means that
there is an Order table and an Item table with a 1:many relationship between
Orders and Items.

Here is another example:
"Person lives at Address"
This means that there will be two tables with a 1:1 relationship between the
tables Person and Address.

Aha, I hear you say, what about rich people who have more than one address?
Good point but in my conceptual model, a person can only be "living at" one
address at a time so the "many addresses" issue is catered for by using a new
fact such as
Person owns Address
For this fact you have a 1:many relationship between Person and Address
Both of these facts can be recorded in just two tables by using appropriate
primary key/foreign key structures.

As you see, there is a lot more to table design that meets the eye. The
first step is to get your facts right. I use a technique called "object-role
modeling (ORM)".

Microsoft has an ORM tool that automatically convert a set of facts in
natural language into a 5th normal form logical table structure. This saves a
lot of time.

You can read more about ORM and Microsoft's ORM tool on my website
http://www.ormfoundation.org

Hope this helps

Ken Evans