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 "Relationships" 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! 7 2011
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(EmployeeID*, FirstName, LastName,...)
Computer(InventoryID*, 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 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 "Relationships" 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.product_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.customer_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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQtQox4echKqOuFEgEQJV+wCfcV/My4Xl3aRY3NJ8Q9QlLyE+8UwAniqL
KTv/SDkEv1noQTerZof0tNqP
=Hzgl
-----END PGP SIGNATURE-----
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********@hotmail.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(EmployeeID*, FirstName, LastName,...) Computer(InventoryID*, 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 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 "Relationships" 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.product_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.customer_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:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
-----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv
iQA/AwUBQtQox4echKqOuFEgEQJV+wCfcV/My4Xl3aRY3NJ8Q9QlLyE+8UwAniqL KTv/SDkEv1noQTerZof0tNqP =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 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********@hotmail.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(EmployeeID*, FirstName, LastName,...) Computer(InventoryID*, 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?
> 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.:
EmployeeComputers
employee_id
computer_id
The Primary Key of EmployeeComputers 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 EmployeeComputers 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 EmployeeComputers table:
Employee EmployeeComputers 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 & EmployeeComputers is from the PK
employee_id to a PART of the PK in EmployeeComputers, employee_id. The
M:1 relationship between EmployeeComputers and Computers is from a PART
of the EmployeeComputers 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_type_code is a many-to-one relation (as a foreign key)
to the FeeTypes.fee_type_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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQtRRl4echKqOuFEgEQLumACeMgcdfpT1CTTmD8Dihyj/VY4tp6QAoJvk
P1UgcYH7NXgCmxtVPs5Ziitw
=7XWH
-----END PGP SIGNATURE-----
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: H Cohen |
last post by:
If a database has relationships establshed between all of the tables
via primary and foreign key constraints, why isn't is possible to make
a...
|
by: Ed |
last post by:
I am running Access 2002 and just ran the built in Access wizard for
splitting a database into a back end (with tables) and front end (with...
|
by: Megan |
last post by:
Hi Everybody-
I know that this is a really, really long post, but I wanted to try to
give you as much background as possible.
So here's a...
|
by: Max |
last post by:
Hi. I really hope someone can help me. Going slowly insane with this
problem.
I have a two Access 2000 databases. One is the backend containing...
|
by: Dixie |
last post by:
I need to delete some relationships in code. How do I know what the names
of those relationships are?
|
by: Robert |
last post by:
I am very new to .net and asp.net. I am trying to design an intranet page
that has a SQL Server 2000 database. I understand how to connect to the...
|
by: salad |
last post by:
I'm curious about your opinion on setting relationships.
When I designed my first app in Access I'd go to Tools/Relationships and
set the...
|
by: celinesuzzarini |
last post by:
Hi all,
I have split my database a while ago, and now, I want to add a table
with relationships to other existing tables.
I open the BE, create...
|
by: ARC |
last post by:
Hello all,
Prior to going live with my app, I have questions on relationships theory.
My prior app was done in Access 97, and I did NOT use...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
|
by: jalbright99669 |
last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
by: Matthew3360 |
last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function.
Here is my code.
...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the...
|
by: Carina712 |
last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
| |