no****@nospam.nospam (Steve Jorgensen) wrote in
<q0********************************@4ax.com>:
The same issue comes up whether it is a design change or an
initial design requirement. It's just easier to describe the
issue in terms of a design change. In either case, there is a
large section of schema in which all the data is normally
partitioned by some owning entity such as a Company.
I have a simpler example of the problem:
Parking Garage Customer Management.
Basic entities: garages, customers, cars
The customers park in only one garage at a time (business rule or
entity attribute?).
The cars table lists the cars for each customer, but have
attributes that are restricted by which garage the car is parked
in.
Is the Garage an attribute of the car or an attribute of the
Customer?
My actual data model (this app was built in 1997, back when I
wasn't very good at this) puts the GarageID in the Customer record
*and* in the Car record. However, the Car table's data fields are
not using RI to restrict what can be entered (e.g., Cars are
assigned numbers specific to a garage, no upper limit but you can't
have two cars assigned the same number) -- it could be done with a
unique index on GarageID and CarNumber, but I actually never
implemented that! I'm not sure why, except that the data model was
clearly not very carefully delineated in my mind, as I still don't
know why GarageID is in both the Customer and Car tables.
Well, yes, I *do* know why -- because that's how I was able to get
it to do what I needed to do. But there's nothing except UI objects
(a combo box with an outer join rowsource based on a table of
possible car numbers) that enforces the business rule.
Now, looking at this schema again (having reworked the app twice in
the last 18 months for new customers), I think that the GarageID in
the customer record doesn't belong there at all. There is no
necessity that one customer would park a car in only one garage.
While this particular organization has all their parking garages in
the city of New York (3 different boroughs), which makes it
unlikely that someone will rent spaces in more than one garage,
it's not at all inconceivable. Indeed, someone might rent a space
in a garage in Brooklyn for home parking and a space in Manhattan
for work parking. The current application would require that the
customer be entered twice, once with each GarageID. That's bad.
So, in this example, Garage is really an attribute of the Car, and
that's pretty clear.
In the case of invoices generated for different companies, it's
more complicated, and not as immediately obvious what is better. No
invoice should have mix items from different companies, as
companies don't (presumably) crossbill for each others' products.
So, it would seem that CompanyID is an attribute of the invoice.
But, as you ask, how to get RI enforcement for the specifics?
I had thought that there would be a messy triangular way to model
this, but I think there is.
I think the flaw is in assuming that tblInvoice.CompanyID and
tblInvoiceDetail.CompanyID actually represent the same data. They
really don't -- even though they point to the same entity in the
relationship model, the CompanyID that restricts which products can
be attached to a particular InvoiceDetail record is not really
serving the same function as the CompanyID in the invoice header.
Think of it this way:
CompanyID in the Invoice header is really BillingEntityID.
CompanyID in the InvoiceDetail is really SupplierID.
Now, in your model, the crucial rule is this:
- No invoice may include products from any supplier but the one
that's creating the invoice.
But that's a rule of your particular application, and is a business
rule that might very well change. Perhaps you *don't* want to build
it into your schema at all because it's subject to change. Now that
the companies have merged, maybe they'll start cross-stocking
inventory from each other and shipping/billing each others'
products. A schema that has your business rule built into it will
need to be changed, while a schema that has no necessity of matchup
between BillingEntityID in tblInvoice and SupplierID in
tblInvoiceDetail will not.
The upshot of that observation (that the crucial problem is not
really a part of the data relationship, but a business rule) is
that it becomes less imperative to enforce this rule in the data
schema itself.
Assuming you're using your usual tools (Access + SQL Server) it
sounds to me like you'd be best to use a normal data schema
(BillingEntityID in the invoice header, SupplierID in the products
table) and have a stored procedure that returns only valid products
for the particular invoice.
In a sense, you've got a 3-tiered structure there, *if* you
consider the data schema to be a base tier and views and stored
procedures to be a middle tier between your schema and your
application. While that's not at all the way the terminology is
usually used, it does indicate the degree to which there's a huge
difference between putting a business rule in the database schema
and putting it in views and stored procedures. While the business
rules are still locked up in your particular back end database
engine (the cardinal violation of two-tier apps that three-tier
apps are intended to address), they are not implemented in the same
degree of fixity. Stored procedures and views are much more
susceptible to alteration as business rules change, much moreso
than the data schema itself.
If through security you restrict all manipulation of the data
tables to the supplied stored procedures and views, you've
accomplished your task, to prohibit the creation of invalid data in
your database.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
