Re: relational reasoning -- why two tables and not one?

paul c wrote:
> I remember when the large canned db apps came out, about twenty years
> ago. They were described as 'enterprise-scale', being successors to the
> specific narrow apps such as payroll, ap, ar, gl that had been around
> since the early 1970's. Never had anything to do with them but I
> gathered that they fell out of favour partly because they usually needed
> massive customization that could be more expensive than "rolling your own".

That hasn't happened anywhere near me. SAP is very widely used, for example, and more and more organisations have given up on bespoke development altogether. SAP's configuration cost is very high, and there's a bad reason why organisations pay that cost. No manager will sign a 7-figure check without a consultant's report saying they made the right decision. Guess who gets to do the work of configuring these behemoths? The very same consultants, of course.

> I doubt if I'm going anywhere useful with this but I've often wondered
> just how many data relationships an efficient economy really needs.

I've been told that the full SAP product install creates around 125,000 tables. If someone can confirm that, I'd be keen to hear it.

> Even if the average business needed a mere one hundred nouns to
> encompass its operations, I guess that could still entail about
> two-times-something-raised-to-the-power-of-a-hundred possible relations,

Could, yes, but usually doesn't. As the number of nouns goes up, the number of join tables goes up only as some logarithm. The thing that really can blow up the complexity is the need to handle historical data in certain ways, because many columns migrate into new tables.

> a very large number, intractable as they say, whereas I believe the very
> largest databases are likely to number their relations in the hundreds.

I hope the SAP example shows that's simply not the case. 500 tables is considered to be medium-sized in my experience.

Last year I used CQL to model motor vehicle insurance claims. The model had 100 nouns, though the database was only 18 tables. It did not include most of the complexity of motor vehicle insurance, did not model the policy, underwriting, insurance history, nor many other facets; and this organisation handled more than twenty other types of insurance.

-- 
Clifford Heath, Data Constellation, http://dataconstellation.com
Agile Information Management and Design