One-One Relationship

[Tom]

Hi,
Do you guys know what's wrong with a one-to-one relationship?

The reason I want to make it like this is that at the very end of the chain,
the set of keys is huge. I want to limit the number of columns to be the
key. i.e. the [company] table has 1 column as the key. The [employee]
table will have 2 columns as the key.

e,g,
If I add a [sale] table to the [company]-[employee] relationship, the third
table
will have 3 columns as the key -- "company id", "employee id", and "sale
id".

(e.g.)
I have a company with many employees and computers. But instead of classify
all these, I just want to call all these as an entity. A company is an
entity. An employee is just another entity. etc.

So, instead of a one-to-many:
[company]---*[employee]---*[sale]
|
|
*
[computer]

I make it one-to-one.

[entity]---*[entity]

If I want to know the name and address of the entity "employee", I will have
a 1-to-1 table [employee] to look up the information for this employee
entity.

[entity]---*[entity]
|
|
[company]
|
|
[employee]
|
|
[computer]
|
|
[sale]
--

There is no answer.
There has not been an answer.
There will not be an answer.
That IS the answer!
And I am screwed.
Deadline was due yesterday.

There is no point to life.
THAT IS THE POINT.
And we are screwed.
We will run out of oil soon.

[--CELKO--]

>> I want to limit the number of columns to be the key. <<

You missed the whole point of a data model. It is supposed to reflect
reality, and not your wishes. If the entities have a key that is made
of (n) atributes, then you have to put the proper constraints in the
DDL.

have a company with many employees and computers. But instead of classify all these, I just want to call all these as an entity. <<

Kind of like a small child who uses the word "THINGIE" for everything
in creation? Please read a book on data modeling before you code
anything.

"To be is to be something n particular; to be nothing in particular or
to be everythign in general is to be nothing at all" -- Aristotle

[Tom]

Hi,

So, you don't know why one-to-one relationship is bad.
"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

I want to limit the number of columns to be the key. <<
You missed the whole point of a data model. It is supposed to reflect
reality, and not your wishes. If the entities have a key that is made
of (n) atributes, then you have to put the proper constraints in the
DDL.
have a company with many employees and computers. But instead of
classify all these, I just want to call all these as an entity. <<

Kind of like a small child who uses the word "THINGIE" for everything
in creation? Please read a book on data modeling before you code
anything.

"To be is to be something n particular; to be nothing in particular or
to be everythign in general is to be nothing at all" -- Aristotle

[Erland Sommarskog]

Tom (no****@yahoo.c om) writes:

Do you guys know what's wrong with a one-to-one relationship?

The reason I want to make it like this is that at the very end of the
chain, the set of keys is huge. I want to limit the number of columns
to be the key. i.e. the [company] table has 1 column as the key. The
[employee] table will have 2 columns as the key.

And? I don't really understand where you're heading. Yes, you could
invent a superclass called entities, and make everything a heir of
that. Which maybe could make sense for some business problems, but
certainly not all. After all, you are in a relational database, not an
object-oriented one.

And I am not really sure what you mean with one-to-one relationship.
I would take that to mean that for every A there is always a B and
vice versa. I could think of having such a pair of tables if the
set of attributes for an item is huge, and some of them are referenced
far more often than others. But it does not seem like it's that you
are talking about. After all, most companies have more than one employee.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Tom]

Hi,

Someone hands me a spec that states the following:

1)
A Headquater can have zero or many companies.
A Company can have zero or many Stores.
A Store can have zero or many Departments.
A Department can have zero or many Services.
A Service can have zero or many Employees.
An Employee can generate many Sales.

2)
Each of these HQ, Company, Store, etc. can have their own Sales.
The Sale doesn't have to be come from an Employee.

By looking at (1), there is no problem. It is clear. The [Sale] table will
have 7 columns as the key.
However, this doesn't satisfy all (1) or (2). If they have a HQ by itself,
the [Sale] table will only have a "HQ id" and 5 empty columns, and finally a
"Sale id".

Another example is that they can have a Compay without a HQ and the Company
has one or two employees. The sales can be done either by the company or by
the employees. In this case, the [Sale] table will have only the [company
id], [employee id], and a [sale id]. The rest of the 4 columns will be
empty. If the sale is done only by the company, there will be 5 empty
columns.

Bottomline is that any of the combination can generate a sale.

Of course, this setup will not work at all.

I already have a way to do this.
I have a [Reference] table that has a reference number and the combination
of the HQ, Company, etc. I then use the Referernce Number and the Sale Id
as the key for the [Sale] table.

This works great but I am trying to think of something simplier (if any).

The very first thing that come to mind is to generalize these HQ, Company,
Employee, etc. to just "Entity". But this will require a One-to-One
relationship between the [Entity] table and the rest of the [HQ], [Company],
etc. tables. Unless I combine all the columns into one big [Entity] table.


"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Tom (no****@yahoo.c om) writes:

Do you guys know what's wrong with a one-to-one relationship?

The reason I want to make it like this is that at the very end of the
chain, the set of keys is huge. I want to limit the number of columns
to be the key. i.e. the [company] table has 1 column as the key. The
[employee] table will have 2 columns as the key.

And? I don't really understand where you're heading. Yes, you could
invent a superclass called entities, and make everything a heir of
that. Which maybe could make sense for some business problems, but
certainly not all. After all, you are in a relational database, not an
object-oriented one.

And I am not really sure what you mean with one-to-one relationship.
I would take that to mean that for every A there is always a B and
vice versa. I could think of having such a pair of tables if the
set of attributes for an item is huge, and some of them are referenced
far more often than others. But it does not seem like it's that you
are talking about. After all, most companies have more than one employee.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Greg D. Moore \(Strider\)]

"Tom" <no****@yahoo.c om> wrote in message
news:xY******** *******@newssvr 27.news.prodigy .net...

Hi,

So, you don't know why one-to-one relationship is bad.
Oh, no, he does. He just told you why.

What Joe has trouble with is social skills. :-)



"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

I want to limit the number of columns to be the key. <<

You missed the whole point of a data model. It is supposed to reflect
reality, and not your wishes. If the entities have a key that is made
of (n) atributes, then you have to put the proper constraints in the
DDL.

have a company with many employees and computers. But instead of
classify all these, I just want to call all these as an entity. <<

Kind of like a small child who uses the word "THINGIE" for everything
in creation? Please read a book on data modeling before you code
anything.

"To be is to be something n particular; to be nothing in particular or
to be everythign in general is to be nothing at all" -- Aristotle

[Tom]

I can tell. ;)
Just like that guy in Office Space.
"Greg D. Moore (Strider)" <mo************ ****@greenms.co m> wrote in message
news:3Y******** **********@news read2.news.atl. earthlink.net.. .

"Tom" <no****@yahoo.c om> wrote in message
news:xY******** *******@newssvr 27.news.prodigy .net...

Hi,

So, you don't know why one-to-one relationship is bad.

Oh, no, he does. He just told you why.

What Joe has trouble with is social skills. :-)

"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

>>> I want to limit the number of columns to be the key. <<
>
> You missed the whole point of a data model. It is supposed to reflect
> reality, and not your wishes. If the entities have a key that is made
> of (n) atributes, then you have to put the proper constraints in the
> DDL.
>
>>> have a company with many employees and computers. But instead of
>>> classify all these, I just want to call all these as an entity. <<
>
> Kind of like a small child who uses the word "THINGIE" for everything
> in creation? Please read a book on data modeling before you code
> anything.
>
> "To be is to be something n particular; to be nothing in particular or
> to be everythign in general is to be nothing at all" -- Aristotle
>

[dbahooker]

set of keys?

a primary key should always be a single column

always always always

[dbahooker]

one-one relationship ISN'T BAD you jsut need to know where to use it

use it to store salary information sometimes
use it if your table is super wide

use it for a couple of different reasons

[Erland Sommarskog]

Tom (no****@yahoo.c om) writes:

Someone hands me a spec that states the following:

1)
A Headquater can have zero or many companies.
A Company can have zero or many Stores.
A Store can have zero or many Departments.
A Department can have zero or many Services.
A Service can have zero or many Employees.
An Employee can generate many Sales.

2)
Each of these HQ, Company, Store, etc. can have their own Sales.
The Sale doesn't have to be come from an Employee.

By looking at (1), there is no problem. It is clear. The [Sale] table
will have 7 columns as the key. However, this doesn't satisfy all (1) or
(2). If they have a HQ by itself, the [Sale] table will only have a "HQ
id" and 5 empty columns, and finally a "Sale id".

Another example is that they can have a Compay without a HQ and the
Company has one or two employees. The sales can be done either by the
company or by the employees. In this case, the [Sale] table will have
only the [company id], [employee id], and a [sale id]. The rest of the
4 columns will be empty. If the sale is done only by the company, there
will be 5 empty columns.

Bottomline is that any of the combination can generate a sale.

Thanks for that supplementary information. That brought a whole more sense
into it.

I can think of a couple of ways to skin that cat. One is to add
dummy entries on all levels, so if the HQ performs a sale, that is
actually a dummy Employee at a dummy Service etc. That is probably messy.

Your idea of making all into entities is probably better, although I
would probably twist it a bit. The first four levels are an orginisational
hierachy, which should be one table, to permit for any number of
levels. What this service is, I don't know so I ignore it :-). Employees
could be tied to any level in the org-hiearchy. (Most HQs have a few
employees of their own.)

This could lead to sale be performed by an employee or a organisation,
which means that you only have two alternate keys in Sales, which might
be manageable.

Exactly which way to go, depends on the rest of the business domain.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp