A
Alex
What are the advantages of the three types of relationships in a database?
One to One, One to Many, and Many to Many?
One to One, One to Many, and Many to Many?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Sprinks
None, a priori. Their relative advantages come in how well they describe the
natural structure of your data.
For example, to represent an order database, there exists a natural
one-to-many relationship between Customers and Orders, as well as a
one-to-many between Orders and OrderDetails, since you might order several
products at once.
A many-to-many relationships exists between Products and Customers--many
customers might order the same product, and a customer might order many
products.
One-to-ones are relatively rare; usually the fields from one may be simply
added to the other, but there are exceptions. One example, for tracking
human resource information of all employees and special information for all
managers, might separate the fields into two tables, either for conceptual
convenience or security reasons.
So the first step in defining your database is to try to identify the
different types of things (i.e., tables) and their attributes (i.e., fields),
and then noting their natural relationships. See a good reference on
"Database Normalization".
Hope that helps.
Sprinks
natural structure of your data.
For example, to represent an order database, there exists a natural
one-to-many relationship between Customers and Orders, as well as a
one-to-many between Orders and OrderDetails, since you might order several
products at once.
A many-to-many relationships exists between Products and Customers--many
customers might order the same product, and a customer might order many
products.
One-to-ones are relatively rare; usually the fields from one may be simply
added to the other, but there are exceptions. One example, for tracking
human resource information of all employees and special information for all
managers, might separate the fields into two tables, either for conceptual
convenience or security reasons.
So the first step in defining your database is to try to identify the
different types of things (i.e., tables) and their attributes (i.e., fields),
and then noting their natural relationships. See a good reference on
"Database Normalization".
Hope that helps.
Sprinks
S
SirPoonga
There is one advantage I have come across. If you want one of hte two
tables to located on a secure server. Like with online shopping carts,
maybe you will store user login info in a table for all apps on your
server (shopping cart, forum, etc..) but the credit card info and such
should go on the SSL server and that's a one to one witht he user info
table.
tables to located on a secure server. Like with online shopping carts,
maybe you will store user login info in a table for all apps on your
server (shopping cart, forum, etc..) but the credit card info and such
should go on the SSL server and that's a one to one witht he user info
table.
S
SirPoonga
Oh yeah, what sprinks said, check up on database normalization.
Usually 3rd order is fine, I have seen definitions up to the 6th order
though.
Usually 3rd order is fine, I have seen definitions up to the 6th order
though.
L
Lynn Trapp
Each of these relationships describes different real world situations for
handling data. It is not so much a matter of one having an advantage over
the other as it is the different kinds of relationships being needed for
different situations.
One To One -- For every record in TableA there is one and only one record in
TableB
Sometimes certain details about a given record are true about a small number
of the records. Rather than have an excessive number of null fields in a
table, you can create a second table for this information and tie the two
tables together on the same primary key, thus creating a one to one
relationship.
One To Many -- For every record in TableA there is one or more records in
TableB
This is the most common kind of relationship (in fact, it is essential to a
many to many relationship) in database design. In an order management
system, each Order might have many Details. Thus, these two tables would
need to be related as one to many.
Many To Many -- This consists of 2 one to many relationships. Every record
in TableA has one or more records in TableC AND every record in TableB has
one or more records in TableC.
A very common application of this has to do with schools. Students may be
enrolled in many classes and each Class has many students. Thus, these
applications typically end up with tables named something like Students,
Classes, and StudentsInClasses.
HTH,
handling data. It is not so much a matter of one having an advantage over
the other as it is the different kinds of relationships being needed for
different situations.
One To One -- For every record in TableA there is one and only one record in
TableB
Sometimes certain details about a given record are true about a small number
of the records. Rather than have an excessive number of null fields in a
table, you can create a second table for this information and tie the two
tables together on the same primary key, thus creating a one to one
relationship.
One To Many -- For every record in TableA there is one or more records in
TableB
This is the most common kind of relationship (in fact, it is essential to a
many to many relationship) in database design. In an order management
system, each Order might have many Details. Thus, these two tables would
need to be related as one to many.
Many To Many -- This consists of 2 one to many relationships. Every record
in TableA has one or more records in TableC AND every record in TableB has
one or more records in TableC.
A very common application of this has to do with schools. Students may be
enrolled in many classes and each Class has many students. Thus, these
applications typically end up with tables named something like Students,
Classes, and StudentsInClasses.
HTH,
S
SirPoonga
"One To Many -- For every record in TableA there is one or more records
in
TableB
This is the most common kind of relationship (in fact, it is essential
to a
many to many relationship) in database design. In an order management
system, each Order might have many Details. Thus, these two tables
would
need to be related as one to many.
"
This is a really good point. If you need to save space and you know a
field contains a large percent of nulls splitting that into a 1 to 1
table relationship will save space. In access say it was a text field
with the default 50 character limit. For 100,000 records access has to
put aside space for all the records and fields. So it is allocating
space for that field when for the most part it will store null anyway.
Access has to allocate enough space for 50,000,000 characters then. Say
only 100 records actually contain data, now splitting that into another
table access now has to allocate only 5000 characters.
in
TableB
This is the most common kind of relationship (in fact, it is essential
to a
many to many relationship) in database design. In an order management
system, each Order might have many Details. Thus, these two tables
would
need to be related as one to many.
"
This is a really good point. If you need to save space and you know a
field contains a large percent of nulls splitting that into a 1 to 1
table relationship will save space. In access say it was a text field
with the default 50 character limit. For 100,000 records access has to
put aside space for all the records and fields. So it is allocating
space for that field when for the most part it will store null anyway.
Access has to allocate enough space for 50,000,000 characters then. Say
only 100 records actually contain data, now splitting that into another
table access now has to allocate only 5000 characters.
R
Rick Brandt
SirPoonga said:"One To Many -- For every record in TableA there is one or more
records in
TableB
This is the most common kind of relationship (in fact, it is essential
to a
many to many relationship) in database design. In an order management
system, each Order might have many Details. Thus, these two tables
would
need to be related as one to many.
"
This is a really good point. If you need to save space and you know a
field contains a large percent of nulls splitting that into a 1 to 1
table relationship will save space. In access say it was a text field
with the default 50 character limit. For 100,000 records access has
to put aside space for all the records and fields. So it is allocating
space for that field when for the most part it will store null anyway.
Access has to allocate enough space for 50,000,000 characters then.
Say only 100 records actually contain data, now splitting that into
another table access now has to allocate only 5000 characters.
Access does not store un-used characters. Sub-Classing like this does save
some space, but not in the amount you are indicating.
S
SirPoonga
Right, because the database file grows. I thought it grows by
allocating space for so many records, then when more records are needed
it adds on more allocated space for a certain number of records and so
forth.
allocating space for so many records, then when more records are needed
it adds on more allocated space for a certain number of records and so
forth.
J
John Vinson
In access say it was a text field
with the default 50 character limit. For 100,000 records access has to
put aside space for all the records and fields.
Well... actually, it doesn't. In SQL or ORACLE terms, an Access Text
field is more like a "varchar" - trailing blanks are not stored, and a
null value doesn't actually take up any space at all.
The much more important reason is data validity.
John W. Vinson[MVP]
S
SirPoonga
I'm not saying it is storing the blanks. I am saying access is
allocating disk space for the records but not storing the data.
Ever notice how sometimes when you add one record the file size of the
database grows abit and sometimes it does? And when you delete records
the file size stays the same. That's because Access is allocating disk
space for the records and not deleting that disk space until you
compact and repair the db. So, the point of specifying the size of a
text field is so access knows how much space to allocate for that
field. Allocating it and storing information in it are two seperate
tasks.
allocating disk space for the records but not storing the data.
Ever notice how sometimes when you add one record the file size of the
database grows abit and sometimes it does? And when you delete records
the file size stays the same. That's because Access is allocating disk
space for the records and not deleting that disk space until you
compact and repair the db. So, the point of specifying the size of a
text field is so access knows how much space to allocate for that
field. Allocating it and storing information in it are two seperate
tasks.