table design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

swiftsmail@yahoo.com - 29 Jan 2005 21:11 GMT

I am having problem with a table design. The table is for
Quality measures. A couple of the attributes for the
Quality measures table are the Company name and Group
name.
Each Company can have multiple groups but a group only
belongs to one particular company so that's a one to many
relationship that exists for the Group table.

The problem is that while a Quality Measure belongs to a
particulare Company, some of the Measures don't belong to
a Group. So I am worried about queries, that if pulled
from the group table, would be result in inaccuraties.

Reply to this Message

Tim Ferguson - 29 Jan 2005 21:27 GMT

> The problem is that while a Quality Measure belongs to a
> particulare Company, some of the Measures don't belong to
> a Group.

You need to be much, much, clearer in your own mind before trying to
model this. "Belongs to" can be a very specific type of relationship or
a general portmanteau word that covers lots of different things.

So, what _exactly_ is the relationship between Measures and Companies:
the company PaidFor it, or OwnsCopyright to it; or is the the SubjectOf
or the CarrierOutOf it?

And, separately, what is the nature of the relationship(s) between the
Measure and the Group. One is clearly this:

The Group to which belongs the Company that (Whatever)ed the Measure.

but you obviously have some extra kind of relationship in mind. What
about

The Group that Commissioned the Measure
The Group that was InChargeAtTheTime of the Measure
The Group that will be ResponsibleForCorrecting the Measure

and so on. Once you have described precisely what you are modelling, the
rest is easy! Just call the relationship (or the FK, if it's a one-to-
many) after the natural English word or phrase.

Hope that helps

Tim F

Reply to this Message

anonymous@discussions.microsoft.com - 29 Jan 2005 21:49 GMT

very true. thanks for the help. Two of the measures can
apply to a group within a company and or the company as a
whole. This is making strict classification difficult. See
what I mean?

>-----Original Message-----
>
[quoted text clipped - 31 lines]
>
>.

Reply to this Message

Tim Ferguson - 30 Jan 2005 18:56 GMT

<anonymous@discussions.microsoft.com> wrote in news:107c01c5064c$61b4fee0
$a501280a@phx.gbl:

> Two of the measures can
> apply to a group within a company and or the company as a
> whole. This is making strict classification difficult. See
> what I mean?

Not really. If you have different types of measures that relate in
different ways to other entities, then you may in fact be looking at more
than one table of QualityMeasures. Try googling for SubTyping methods in
relational database design.

All the best

Tim F

Reply to this Message