Re: One-One Relationship

Tom (nospam@xxxxxxxxx) 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@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: One-One Relationship
    ... A Headquater can have zero or many companies. ... A Service can have zero or many Employees. ... Each of these HQ, Company, Store, etc. can have their own Sales. ... The Sale doesn't have to be come from an Employee. ...
    (comp.databases.ms-sqlserver)
  • Re: Radio Shaft, Indeed
    ... Yes, that's right, these employees found out they lost their ... edit out the personalization first. ... then an announcement on each of the days of the sale. ... email accounts that they never check, ...
    (misc.consumers)
  • Re: part-time job idea
    ... on a nice get-together with your employees at Zeitgeist. ... That had cost Microsoft a sale in the late 90's. ... Embedded Systems Conference in San Jose. ...
    (sci.electronics.design)
  • Re: Tricky LONG formula, need a shorter version, there has to be a way
    ... You must have forgotten that zero ... etc) if there is a 1 they have made a sale from that appointment, if nothing, ... making a sale in Auckland). ... And is spread out over ...
    (microsoft.public.excel.misc)
  • Re: Question on selling business equipment.
    ... claim that $8000 as income? ... its adjusted cost basis is ... You will use that adjusted basis of zero on form 4797 to report ... sale of property used in your trade or business. ...
    (misc.taxes)