|
|
Posted by Erland Sommarskog on 10/12/05 00:41
Tom (nospam@yahoo.com) 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, 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]
|