|
|
Posted by Tom on 10/11/05 02:05
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" <esquel@sommarskog.se> wrote in message
news:Xns96EBF072AC038Yazorman@127.0.0.1...
> Tom (nospam@yahoo.com) 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, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
Navigation:
[Reply to this message]
|