Re: One Ring to Bind Them

Marshall:

> "Marshall Spight" <mspight_at_dnai.com> wrote...
> > "Bill H" <wphaskett_at_THISISMUNGEDatt.net> wrote...
>
> I dunno. If every invoice has exactly one vendor, I think "containment"
> is a pretty good term to describe that relationship. (And a popular one
> as well.) Do you have a preferred term for "every x has an associated
> y?"

My apologies. I thought you defined containment differently. This will do for me, although I'd probably not use that term for many to many relationships. But if you like, I'll stick with it.

> > What exactly is this relationship and how is it stored? I can store the
> > invoice#s within the vendor in the vendor table.
>
> I want to make sure I understand: when you say "invoice#***s***" (I
> especially note the "s") you mean to say that the vendors
table/file/collection
> has an attribute/field that is a **list** of invoice numbers? Or is it a
list
> of invoices?

A list of invoices numbers, in my example, is correct. It may look like:

Field# Contents.....
005 1272]7214-2]B715Z]1714A16

The order doesn't matter in this example. So the record set not only contains data but arrays/lists/collections/whatever. You get the point. The dbms tools work with this. I can then reference data in the related table as though the data were local to the referenced table (e.g. list vendor inv# invdate invdesc invamount...) and this will list the vendors with their associated invoice data extracted from the related table.

> > This defines a relationship in the MV model (although there are
> > a number of other ways to do so).
>
> As an aside: can you enumerate the different ways?

An index can define a relationship, so can a constraint and a function. I can also create custom rule relationships that operate off a trigger.

> > How is this relationship going to be exposed? An example would be
> > to create a virtual field definition in the vendor table so that when
asked,
> > will deliver the list of invoices associated with this vendor and any
data
> > contained within the invoice table.
>
> The term "virtual" here; what does it mean? Is there an online reference
> you like that I could use to read about this?

I alluded to it above. It is a field definition that doesn't reference data in the referenced table but references data in a related table. I gave an example of the vendor table list that actually returns data from the related invoice table.

> > The relationship is stored in the relational database but not really
like it
> > is stored in the MV database. This is true because the MV model treats
> > everything like regular data; unlike the RD model. As such, everything
is
> > stored in the database tables right along with all the other data;
names,
> > addresses, relationships, metadata, functions, constraints, stored
> > procedures, application code, compiled code, etc. All MV tools, like RD
> > tools, are available for these additionally defined data; it's just
stored
> > with all other data in the exact same formats.
>
> The distinction you're drawing is that in addition to all the stuff that
> both model store, (names, addresses, relationships, metadata, functions,
> constraints, stored procedures) the MV model additionally stores
> application code, compiled code, MV tools, etc. Is that right?

Yes. When I deliver an application the entire application is delivered in the database, including code, table structure, field definitions, etc, etc, etc. Users can access and run it without loading any application code onto their workstations.

> Again, it's something I'd like to try out. Can you recommend a free
> solution; the mysql of MV?

Try the following:

http://www-306.ibm.com/software/data/u2/universe/

http://www.jbase.com/products/jbase_download.html

http://www.revelation.com/SOFTWARE.NSF/06fb58066b4ed717852564030070163e?OpenView

There are some others but this will get anyone started. Remember, the product is both a dbms and an application environment. Don't expect it to just be a dbms where one starts the service and accesses it via SQL (although one can). I personally use the IBM product and another one but you can't get the other one for free, so I won't send you to their web site. :-)

> > What makes this different is only that these tables are usually part of
the
> > database structure of the production data. So, you'd have tables for
> > constraints, stored procedures, relationships, metadata, application
code,
> > and data all within a single database structure built for an
application.
> > The RD model would normally keep this kind of data separate from the
> > production data within its own special system tables.
>
> This separation you describe is not much of a separation.

Remember this is both a dbms and an application server product. It has more capabilities and additional tools.

> What if you have two comparatively unrelated applications that work
> against the same schema; both applications are in the dbms; one
> application enforces a constraint and one doesn't (for whatever reason:
> a bug, or the programmer just forgot about it.) Wouldn't that be a
> pathway for data corruption to enter the system?

Under this scenario, one would have to design a non-RD model dbms application like any other good application where the API's are designed to return and/or do stuff via calls, where the user interface is separate from the business rules. However, there's just no solution for multiple applications sharing data in a dbms and having different business rules (constraints/relationships/etc) that effect the data. Which is controlling? Can application A build the business rule APIs and application B use them?

I'd say this is an ideal place for the RD model dbms, as many people who don't know the business well can do the development and queries.

> > Ah, excuse me? One reuses check#s in different accounts all the time.
One
> > reuses invoice#s for different vendors all the time too. To include the
> > transaction# in the account table is to do nothing different than needs
to
> > be done anyway to define a relation; A > B and B < A.
>
> I agree up until the last sentence. You don't need both A > B and B < A
> to define a relation; you only need one or the other. Likewise, you
> don't need a list of invoice numbers in the accounts table *and* an
> account number in the invoices table; that's a denormalization that
> will lead to corruption. You need one or the other, but both is bad,
> (unless they are just different views on the same data. Are they?
> Or are they stored separately, and able to become out of sync.)

My last sentence only meant to describe my defined relation between the vendor and the invoice and the invoice and the vendor; nothing more. :-)

If the invoice is related to the vendor then the invoice has to have access to the vendor, somehow, somewhere, plain and simple. If not, I could never get a list of invoices with the vendor# too. The same is true with the vendor. I described how to do this, explicitly. There's no need to talk about data corruption, considering the syncronization tools are available and, hopefully, are in place.

These are the kinds of issues, however, we have to face as DBAs and developers, no matter what we're developing with/in. True?

> > Redundancy? Storing
> > the transaction#s in the account saves having to store the "transaction
to
> > account" relationship, as it is already defined by the transaction key.
So
> > this reduces redundancy.
>
> Uh, no. I mean, it's less redundancy that storing it three times, but it's
> more than just storing it once.

I'm more inclined to design for errors with a "little" redundancy. :-) (although it isn't absolutely necessary).

> If you don't store the same information more than once, then
> the entire concept of "synchronization code" (first time I've
> heard the term) is unnecessary.

If the RD model does a cascading delete of a vendor and its association invoices, it _has_ to know the invoices associated with the vendor too! I write the code once (15 years ago) and use it all the time in this environment. That's because the mvDbms environment is more than a dbms and considers the application side of its nature.

Some things have to be written because it is "assumed" better to do so in the application (hey, what can I say). We've developed applications where bad things happen to the hardware and, thus, the data. In accounting data some redundancy is an absolute requirement, if for no other reason than to know of problems and be able to rebuild.

Is this assumption good? I don't know. Probably mostly yes in some development environments and mostly no in other development environments.

> Hmmm. Data management is something that is very useful to
> business, but it is not business-oriented in and of itself.
> Same with adding up columns of numbers.
>
> You mean because they don't understand SQL? I don't get why we're
> talking about business people here; we're discussing data management.

This is one view. Mine is different because I have to pay bills and employees. If there's not enough cash I don't get paid. So, for me, it _is_ all about business. :-)

> > I can set a trigger to enforce integrity within the bank account table
so if
> > a bank transaction is cleared, the uncleared reference to it within the
bank
> > account table is removed. So, right here I've set both a trigger and
> > constraint on a relation at the same time. I know the RD model
accomplishes
> > the same task but differently.
>
> Do you have to set these up manually every place a bank transaction clear
> is invoked, or do you just do it once?

No. It's done once in the appropriate table definition, or perhaps the field definition.

> > I cannot emphasize this enough; the MV model is located centrally! The
> > application server and dbms server reside within the same environment,
on
> > the same machine. Therefore, all constraints are enforced centrally.
The
> > centralized application APIs can be called from outside the application.
> > Additional constraints can be developed to provide service to more than
one
> > application and to meet ever-changing requirements.
>
> Where can I read more about this intriguing concept?

You can read a lot of information about it at:

http://www-306.ibm.com/software/data/u2/

and you can google to newsgroups and read comp.databases.pick. It's been around forever and has a lot of fun stuff. And they're mostly polite, except for the occasional individual who forgot to take his lithium. :-)

> > And here I was thinking I was answering your queries directly, albeit in
a
> > slightly different perspective. Perhaps my writing skills, and clarity
of
> > thought, will improve with time. :-)
>
> Actually, I found this most recent message quite comprehensible. Also,
> I want to thank you for hanging in with my questions as my frustration
> grew. You are a gentleman, sir, and the world and this newsgroup needs
> more gentlemen. (And ladies, of course.)

Thank you for your kind words and I look forward to passing them on to the next person on this list. :-)

Bill Received on Sun Jul 11 2004 - 04:51:22 CEST