Relationships confusion

[davegb]

I'm totally new to relational database design. My boss has asked me to
create a database of information on the employees in our group. Seemed
to me like a simple application to learn the ropes. A couple of things
are confusing me.
The first is "Relationsh ips" and "Joins". Are they different names for
the same thing? If not, what is the difference? I have a copy of the
Access 2000 Bible, and it says, "When you create a relationship between
2 tables, Access automatically creates a join line from one table to
another". That sounds like they're the same, or that one, probably
Joins, is a subset of Relationships. He also goes into lengthy
explanations of each, as though they are different. Does anyone have a
straightforward explanation (or excluding that, a not-so-straighforward
explanation) of what they are, and what their relationship is?
The other problem I'm having is creating relationships in the
Relationships window. It seems strange to me that Access decides which
relationships are 1:1, 1:many, or indeterminate, based on what fields I
choose to link. Which creates a quandary for me. One of the things I'm
trying to do with the employee database is to track who has what
computer. It would be a simple 1:1 except we assign laptops as needed
to anyone who needs one. So there is a one to many relationship between
people and computers. But if I link from Employee ID to Computer ID,
the way that makes the most sense to me, I am forced to use a 1:1
relationship. Nobody can have more than one computer at a time. It
seems that I have to create a second computer ID field in the table,
one that is not the primary key, to be able to create this
relationship. Is this how it's done? If not, how is it correctly done?
(another possible solution would be to have separate tables and
relationships for desktop and laptop computers, but this would make
inventorying them more difficult)
Thanks for your help!

[pietlinden]

I'll take the "person-uses-computer" part. If one person gets one and
only one computer, you *could* theoretically have a one-to-one
relationship between them, but then there's that nasty word "uses".
When i map real world scenarios so that I can create a database, I
generally look for verbs first (which are generally relationships) and
then the nouns that the verbs connect. In this case, it's
person (subject) uses (verb) computer (object)

so then the question becomes:
Can one person use more than one computer (at one time)?
Can one computer be used by more than one person (at one time)?

If these are true, then you would have a design something like this:

Person(Employee ID*, FirstName, LastName,...)
Computer(Invent oryID*, SerialNo, Make, Model...)
Uses(EmployeeID *, InventoryID*, IssueDate*, ReturnDate)
* - indicates primary key.

Because having a primary key guarantees uniqueness, if you join Person
to Uses, you will automatically get a 1-many relationship.

No need for separate tables for laptop and desktop computers, really.
You can put a flag in there (Yes/No or whatever) that indicates which
type of computer it is. It depends if you're interested in all the
other peripheral stuff as well. (external monitors etc, which desktops
have and laptops generally don't).

Another note on the "uses" table - if you don't care to record who used
the computer/equipment previously, then you could join the computer and
person tables by putting the employeeID field into the computer table.
then there's an implied rule that only one person can use it at a time,
and a person can have only one. That's a bit restrictive though,
usually. And harder to expand later.

A book you might want to look at it Michael Hernandez's "Database
Design for Mere Mortals"
http://www.amazon.com/exec/obidos/tg...glance&s=books

Sorry, I only answered part of the question, but there are LOTS of
questions in there!
HTH,
Pieter

[MGFoster]

davegb wrote:

I'm totally new to relational database design. My boss has asked me to
create a database of information on the employees in our group. Seemed
to me like a simple application to learn the ropes. A couple of things
are confusing me.
The first is "Relationsh ips" and "Joins". Are they different names for
the same thing? If not, what is the difference? I have a copy of the
Access 2000 Bible, and it says, "When you create a relationship between
2 tables, Access automatically creates a join line from one table to
another". That sounds like they're the same, or that one, probably
Joins, is a subset of Relationships. He also goes into lengthy
explanations of each, as though they are different. Does anyone have a
straightforward explanation (or excluding that, a not-so-straighforward
explanation) of what they are, and what their relationship is?
The other problem I'm having is creating relationships in the
Relationships window. It seems strange to me that Access decides which
relationships are 1:1, 1:many, or indeterminate, based on what fields I
choose to link. Which creates a quandary for me. One of the things I'm
trying to do with the employee database is to track who has what
computer. It would be a simple 1:1 except we assign laptops as needed
to anyone who needs one. So there is a one to many relationship between
people and computers. But if I link from Employee ID to Computer ID,
the way that makes the most sense to me, I am forced to use a 1:1
relationship. Nobody can have more than one computer at a time. It
seems that I have to create a second computer ID field in the table,
one that is not the primary key, to be able to create this
relationship. Is this how it's done? If not, how is it correctly done?
(another possible solution would be to have separate tables and
relationships for desktop and laptop computers, but this would make
inventorying them more difficult)
Thanks for your help!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Relationships are logical relationships between tables. Joins are used
in queries to indicate how you want to the relate one table to another
for that particular query. E.g.:

Products:
Columns: product_code - integer - some product code PK
definition - Text - a description of the product

Customers:
Columns: customer_id - integer - some number PK
first_name - text
last_name - text
(other columns)

Orders:
Columns: product_code - integer - related to product_code in Products
order_date - date
customer_id - integer
quantity - integer

Primary Key in Orders would be product_code, order_date and customer_id,
which means that that customer can only have one order for that one
product per day. (For this example this is a very simplified Orders
table.) Primary Keys and Unique columns only have one unique value per
row (aka record) in that column (aka field).

The Orders.product_ code can be set up as related to the
Products.produc t_code as a Foreign Key. IOW, before a product_code can
be entered into Orders there has to be that same code in Products. This
is a 1 to many relationship (1 product but many orders of that product).
There is another 1 to many relationship between Customers.custo mer_id
and Orders.customer _id (1 customer but many orders by that customer).

When you create a query you indicate how you want each table to relate
to each other by JOINs. E.g.:

SELECT C.customer_id, P.product_code, O.order_date

FROM (Orders As O
INNER JOIN Products P
ON O.product_code = P.product_code)
INNER JOIN Customers As C
ON O.customer_id = C.customer_id

The INNER JOIN is a type of join that indicates that the query should
return only data that is in both tables, that have the same values in
the indicated columns. The indicated columns are those named in the ON
clause.

The types of relationships (1 to 1, 1 to many, indeterminate) can be
determined by this chart (the left side of the realtionship (1 to 1) is
table 1, the right side is table 2):

Table 1 -> PK Unique Not unique
------------------------------------------------
Table 2
PK 1 to 1 1 to 1 Many to 1

Unique 1 to 1 1 to 1 Many to 1

Not unique 1 to Many 1 to Many Indeterminate

Unique means there is a "No Duplicates" index on the column. [Primary
Keys and Unique indexes can be composed of more than column. For this
example, I'm just using one column].

This means if the column in table 1 is the PK (Primary Key) and the
column in table 2 is not unique, then the relationship is 1 to many.
The one side being the PK side and the many side being the not unique
side.

When you create a query, you don't have to join the tables on the
related columns, you can use any columns you want (as long as the data
types of both columns are the same). Sometimes that makes sense (like
when you're comparing unrelated dates between tables). Most of the time
it doesn't (IOW, usually a good idea to join on related columns).

Read the Access Help articles on Database Design and/or get a copy of a
database design book for more info. I usually recommend _Database
Design for Mere Mortals_ by Hernandez as a good starting book for db
design.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQox4echKq OuFEgEQJV+wCfcV/My4Xl3aRY3NJ8Q9 QlLyE+8UwAniqL
KTv/SDkEv1noQTerZof 0tNqP
=Hzgl
-----END PGP SIGNATURE-----

[davegb]

Thanks for your reply, Pietlin. It definitely helps!
If I'm understanding you, the best approach would be to create an
additional table, "Uses", rather than try to connect the existing
tables, "computers" and "employees" directly. Then enter each person's
ID no and the corresponding computer ID into the "Uses" table. I guess
that then, I circumvent the whole issue of relationships, I don't have
to create any at all! Never would have thought that up by myself. It
still begs the question, "What are relationships for?", but solves my
problem in the short term.
Thanks again,
Dave

pi********@hotm ail.com wrote:

I'll take the "person-uses-computer" part. If one person gets one and
only one computer, you *could* theoretically have a one-to-one
relationship between them, but then there's that nasty word "uses".
When i map real world scenarios so that I can create a database, I
generally look for verbs first (which are generally relationships) and
then the nouns that the verbs connect. In this case, it's
person (subject) uses (verb) computer (object)

so then the question becomes:
Can one person use more than one computer (at one time)?
Can one computer be used by more than one person (at one time)?

If these are true, then you would have a design something like this:

Person(Employee ID*, FirstName, LastName,...)
Computer(Invent oryID*, SerialNo, Make, Model...)
Uses(EmployeeID *, InventoryID*, IssueDate*, ReturnDate)
* - indicates primary key.

Because having a primary key guarantees uniqueness, if you join Person
to Uses, you will automatically get a 1-many relationship.

No need for separate tables for laptop and desktop computers, really.
You can put a flag in there (Yes/No or whatever) that indicates which
type of computer it is. It depends if you're interested in all the
other peripheral stuff as well. (external monitors etc, which desktops
have and laptops generally don't).

Another note on the "uses" table - if you don't care to record who used
the computer/equipment previously, then you could join the computer and
person tables by putting the employeeID field into the computer table.
then there's an implied rule that only one person can use it at a time,
and a person can have only one. That's a bit restrictive though,
usually. And harder to expand later.

A book you might want to look at it Michael Hernandez's "Database
Design for Mere Mortals"
http://www.amazon.com/exec/obidos/tg...glance&s=books

Sorry, I only answered part of the question, but there are LOTS of
questions in there!
HTH,
Pieter

[davegb]

MGFoster wrote:

davegb wrote:

I'm totally new to relational database design. My boss has asked me to
create a database of information on the employees in our group. Seemed
to me like a simple application to learn the ropes. A couple of things
are confusing me.
The first is "Relationsh ips" and "Joins". Are they different names for
the same thing? If not, what is the difference? I have a copy of the
Access 2000 Bible, and it says, "When you create a relationship between
2 tables, Access automatically creates a join line from one table to
another". That sounds like they're the same, or that one, probably
Joins, is a subset of Relationships. He also goes into lengthy
explanations of each, as though they are different. Does anyone have a
straightforward explanation (or excluding that, a not-so-straighforward
explanation) of what they are, and what their relationship is?
The other problem I'm having is creating relationships in the
Relationships window. It seems strange to me that Access decides which
relationships are 1:1, 1:many, or indeterminate, based on what fields I
choose to link. Which creates a quandary for me. One of the things I'm
trying to do with the employee database is to track who has what
computer. It would be a simple 1:1 except we assign laptops as needed
to anyone who needs one. So there is a one to many relationship between
people and computers. But if I link from Employee ID to Computer ID,
the way that makes the most sense to me, I am forced to use a 1:1
relationship. Nobody can have more than one computer at a time. It
seems that I have to create a second computer ID field in the table,
one that is not the primary key, to be able to create this
relationship. Is this how it's done? If not, how is it correctly done?
(another possible solution would be to have separate tables and
relationships for desktop and laptop computers, but this would make
inventorying them more difficult)
Thanks for your help!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Relationships are logical relationships between tables. Joins are used
in queries to indicate how you want to the relate one table to another
for that particular query. E.g.:

Products:
Columns: product_code - integer - some product code PK
definition - Text - a description of the product

Customers:
Columns: customer_id - integer - some number PK
first_name - text
last_name - text
(other columns)

Orders:
Columns: product_code - integer - related to product_code in Products
order_date - date
customer_id - integer
quantity - integer

Primary Key in Orders would be product_code, order_date and customer_id,
which means that that customer can only have one order for that one
product per day. (For this example this is a very simplified Orders
table.) Primary Keys and Unique columns only have one unique value per
row (aka record) in that column (aka field).

The Orders.product_ code can be set up as related to the
Products.produc t_code as a Foreign Key. IOW, before a product_code can
be entered into Orders there has to be that same code in Products. This
is a 1 to many relationship (1 product but many orders of that product).
There is another 1 to many relationship between Customers.custo mer_id
and Orders.customer _id (1 customer but many orders by that customer).

When you create a query you indicate how you want each table to relate
to each other by JOINs. E.g.:

SELECT C.customer_id, P.product_code, O.order_date

FROM (Orders As O
INNER JOIN Products P
ON O.product_code = P.product_code)
INNER JOIN Customers As C
ON O.customer_id = C.customer_id

The INNER JOIN is a type of join that indicates that the query should
return only data that is in both tables, that have the same values in
the indicated columns. The indicated columns are those named in the ON
clause.

The types of relationships (1 to 1, 1 to many, indeterminate) can be
determined by this chart (the left side of the realtionship (1 to 1) is
table 1, the right side is table 2):

Table 1 -> PK Unique Not unique
------------------------------------------------
Table 2
PK 1 to 1 1 to 1 Many to 1

Unique 1 to 1 1 to 1 Many to 1

Not unique 1 to Many 1 to Many Indeterminate

Unique means there is a "No Duplicates" index on the column. [Primary
Keys and Unique indexes can be composed of more than column. For this
example, I'm just using one column].

This means if the column in table 1 is the PK (Primary Key) and the
column in table 2 is not unique, then the relationship is 1 to many.
The one side being the PK side and the many side being the not unique
side.

When you create a query, you don't have to join the tables on the
related columns, you can use any columns you want (as long as the data
types of both columns are the same). Sometimes that makes sense (like
when you're comparing unrelated dates between tables). Most of the time
it doesn't (IOW, usually a good idea to join on related columns).

Read the Access Help articles on Database Design and/or get a copy of a
database design book for more info. I usually recommend _Database
Design for Mere Mortals_ by Hernandez as a good starting book for db
design.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtQox4echKq OuFEgEQJV+wCfcV/My4Xl3aRY3NJ8Q9 QlLyE+8UwAniqL
KTv/SDkEv1noQTerZof 0tNqP
=Hzgl
-----END PGP SIGNATURE-----

Thanks for your reply, MG! Some of it's starting to make more sense.

Relationships are logical relationships between tables. Joins are used in queries to indicate how you want to the relate one table to another
for that particular query.
That explanation alone helps immensely.

Primary Keys and Unique indexes can be composed of more than column. For this
example, I'm just using one column

This part I don't understand at all. How can a primary key be more than
one column at a time? I thought the whole purpose of a Primary Key was
that that field was unique and could be used to identify a particular
record. Can you give me an example where a Primary Key would be more
than one field?

I'm still not clear on why connecting 2 primary keys requires a 1:1
relationship. If I link Employee ID 0001 to computer ID 0002, and those
are the primary fields in both tables, by limitation of database
theory, no person can have more than one computer. Of course, we both
know this isn't true in the office. So then we have to do some kind of
elaborate workaround, as Pietl suggested, to get the database to
reflect reality? I guess if that's how it works, I can get used to it.
I have always had a problem working with tools that seem to contradict
reality. At least at this point, it seems that databases are like
accounting - they have little to do with reality, and we have to force
what it is we're trying to do to fit a set of rules set up for some
other reason entirely. We end up with the cart before the horse!
Regardless of the limitations of database theory, I certainly
appreciate your help. I did take a look at the book you & Pietl
suggested on Amazon. The part I could see online looked interesting.
Will look at it at the bookstore as soon as I can find the time.

[davegb]

davegb wrote:

Thanks for your reply, Pietlin. It definitely helps!
If I'm understanding you, the best approach would be to create an
additional table, "Uses", rather than try to connect the existing
tables, "computers" and "employees" directly. Then enter each person's
ID no and the corresponding computer ID into the "Uses" table. I guess
that then, I circumvent the whole issue of relationships, I don't have
to create any at all! Never would have thought that up by myself. It
still begs the question, "What are relationships for?", but solves my
problem in the short term.
Thanks again,
Dave

pi********@hotm ail.com wrote:

I'll take the "person-uses-computer" part. If one person gets one and
only one computer, you *could* theoretically have a one-to-one
relationship between them, but then there's that nasty word "uses".
When i map real world scenarios so that I can create a database, I
generally look for verbs first (which are generally relationships) and
then the nouns that the verbs connect. In this case, it's
person (subject) uses (verb) computer (object)

so then the question becomes:
Can one person use more than one computer (at one time)?
Can one computer be used by more than one person (at one time)?

If these are true, then you would have a design something like this:

Person(Employee ID*, FirstName, LastName,...)
Computer(Invent oryID*, SerialNo, Make, Model...)
Uses(EmployeeID *, InventoryID*, IssueDate*, ReturnDate)
* - indicates primary key.

Because having a primary key guarantees uniqueness, if you join Person
to Uses, you will automatically get a 1-many relationship.

No need for separate tables for laptop and desktop computers, really.
You can put a flag in there (Yes/No or whatever) that indicates which
type of computer it is. It depends if you're interested in all the
other peripheral stuff as well. (external monitors etc, which desktops
have and laptops generally don't).

Another note on the "uses" table - if you don't care to record who used
the computer/equipment previously, then you could join the computer and
person tables by putting the employeeID field into the computer table.
then there's an implied rule that only one person can use it at a time,
and a person can have only one. That's a bit restrictive though,
usually. And harder to expand later.

A book you might want to look at it Michael Hernandez's "Database
Design for Mere Mortals"
http://www.amazon.com/exec/obidos/tg...glance&s=books

Sorry, I only answered part of the question, but there are LOTS of
questions in there!
HTH,
Pieter

It also occurred to me that another way to create this relationship
would be to have 2 computer ID fields in the computer table. Make one
the primary key, the other one not. Then link people to the non-primary
field one and create a one-to-many relationship. Is that a standard way
of getting around this limitation?

[MGFoster]

> Thanks for your reply, MG! Some of it's starting to make more sense.

Relationships are logical relationships between tables. Joins are used
in queries to indicate how you want to the relate one table to another
for that particular query.

That explanation alone helps immensely.

Primary
Keys and Unique indexes can be composed of more than column. For this
example, I'm just using one column

This part I don't understand at all. How can a primary key be more than
one column at a time? I thought the whole purpose of a Primary Key was
that that field was unique and could be used to identify a particular
record. Can you give me an example where a Primary Key would be more
than one field?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A Primary Key is meant to uniquely identify the table row (aka record or
entity). So, let's take a look at the Orders table that I used as an
example:

Orders:
Columns: product_code - integer - related to product_code in
Products
order_date - date
customer_id - integer
quantity - integer

Each attribute (column), by itself, could be referring to many Orders.
E.g.: If we just looked at Orders w/ product_code 25, there may be
thousands of orders w/ that product_code. But, if we combine
product_code, order_date, customer_id, we have a specific order: one
that was made by customer_id X, on order_date Y for product_code 25.
Those three columns values uniquely identify the order; therefore, those
three columns are good candidates for a Primary Key.

I'm still not clear on why connecting 2 primary keys requires a 1:1
relationship. If I link Employee ID 0001 to computer ID 0002, and those are the primary fields in both tables, by limitation of database
theory, no person can have more than one computer.

Since primary keys are unique that means there can only be one row w/
that PK in each table. If there is only 1 unique row per table, that is
a 1:1 relationship.

You're mistakenly thinking that a table has to be related only by the
Primary Keys. Remember the column values have to be LOGICALLY
equivalent. E.g.:

Table 1 Table 2
Animal column Appliance column

giraffe kitchen sink

A giraffe is an animal. A kitchen sink is not an animal. Obviously, an
animal is not an appliance. Therefore, we cannot logically link table 1
to table 2 on those columns.

In your example

Employees table Computers table
employee_id computer_id

Employee_id and Computer_id are supposed to uniquely identify each
entity, respectively. Even though the values of employee_id and
computer_id columns are numbers an employee is not a computer,
therefore, we cannot relate the Employees table to the Computers table
using the "ID" column of each table.

What you'd use to show employees who HAVE computers would be to create
an intersection table that holds both employee_id & computer_id. E.g.:

EmployeeCompute rs
employee_id
computer_id

The Primary Key of EmployeeCompute rs is both employee_id and
computer_id, because one employee can have many computers and one
computer can be had by many employees (obviously, in the "real" world,
there are other things you'd want to put in EmployeeCompute rs to prevent
one computer being held by many employees - like a start & end date).
Now, the relationship between employees and computers is described in
the EmployeeCompute rs table:

Employee EmployeeCompute rs Computers
employee_id 1 -> M employee_id
computer_id M -> 1 computer_id

This is how a many-to-many relationship is set up.

The 1:M relationship between Employee & EmployeeCompute rs is from the PK
employee_id to a PART of the PK in EmployeeCompute rs, employee_id. The
M:1 relationship between EmployeeCompute rs and Computers is from a PART
of the EmployeeCompute rs PK to the Computers PK, computer_id. It isn't
necessary to relate one table to another using the PK in both tables.

You could have a main table that uses Foreign Key (FK) relationship w/ a
"lookup table," like this:

Projects FeeTypes
project_name PK +-----> 1 fee_type_code Unique
start_date PK | definition PK
end_date |
fee_type_code FK M ---+
account_no
... other columns ...

The Projects.fee_ty pe_code is a many-to-one relation (as a foreign key)
to the FeeTypes.fee_ty pe_code. The fee_type_code is NOT the PK of
FeeTypes, but it IS unique, which allows the 1:M relationship to be
created. This relationship means that in the Projects table's one
projects record can only have one of the fee_type_codes stored in
FeeTypes, but, each record can have ANY fee_type_code (IOW, there can be
duplicate fee_type_codes in Projects, but only unique fee_type_codes in
the FeeTypes table).
HTH,
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtRRl4echKq OuFEgEQLumACeMg cdfpT1CTTmD8Dih yj/VY4tp6QAoJvk
P1UgcYH7NXgCmxt VPs5Ziitw
=7XWH
-----END PGP SIGNATURE-----

[Br]

To original poster:

Why is this in two threads? Please don't post the same thing again under
different subject headers.

<>
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response