Is there such a thing as too many relationship?

[H Cohen]

Hi,

I have a corporate database with about 60 different tables that spans
manufacturing, accounting, marketing, etc.

It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields like customer_id
or product_id.

But should I do that?

My question is: Is there such a thing as too many relationships? Can
I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected and simply use
JOINS in my business code?

Thanks,
HC

[--CELKO--]

>> I have a corporate database with about 60 different tables that
spans manufacturing, accounting, marketing, etc. <<

That is not that big for a corporate RDBMS ..

It is possible, but unwieldy, to establish a relationship for each table in the entire database through critical fields [sic] customer_id
or product_id. But should I do that? <<

What do you mean by "establish a relationship"? Build a relationship
table among all the entities in the model?
My question is: Is there such a thing as too many relationships?

Can I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected [sic] and
simply use JOINS in my business code? <<

Conntected? You mean like in a network database with pointer chains?
You even talk about fields, not columns. Things in SQL are
referenced.

Yes, you need to get all of the business rules in your model. The
more you can enforce them with DRI actions and CHECK() constraints,
the better for you and the easier for the programmers that follow.
Otherwise, you data model is incomplete.

[H Cohen]

jc*******@earthlink.net (--CELKO--) wrote in message news:<18**************************@posting.google. com>...

I have a corporate database with about 60 different tables that spans manufacturing, accounting, marketing, etc. <<

That is not that big for a corporate RDBMS ..
It is possible, but unwieldy, to establish a relationship for each table in the entire database through critical fields [sic] customer_id
or product_id. But should I do that? <<

What do you mean by "establish a relationship"? Build a relationship
table among all the entities in the model?
My question is: Is there such a thing as too many relationships?

Can I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected [sic] and
simply use JOINS in my business code? <<

Conntected? You mean like in a network database with pointer chains?
You even talk about fields, not columns. Things in SQL are
referenced.

Yes, you need to get all of the business rules in your model. The
more you can enforce them with DRI actions and CHECK() constraints,
the better for you and the easier for the programmers that follow.
Otherwise, you data model is incomplete.

CELKO,

What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database?

Thanks.

[Joe Celko]

>> What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database? <<

Auxiliary tables, such as the calendar, would not be referenced by
another table.

Working tables used to scrub data before it goes into the schema would
not be referenced by another table. They might have few if any
constraints, so the raw data could be inspected.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Michael K Campbell]

It is possible to have 'too many' relationships... though I doubt that
will be an issue for you. In other words, as everyone knows DRI exists
to help ensure data integrity. It also servers the purpose, as
intimated by Celko, of showing relations between entities...

However, each CHECK/FK creates a bit of overhead. That overhead can
end up being noticeable on VERY large tables (in the GBs and tens of
millions of rows). So there's a fine balance between optimizing
performance in some OLTP environments and keeping data clean/intact.
Good indexing can go a long way to keep all of this in check.

Case in point on the TOO MANY relationships (given all the stuff I
blabbed about above concerning costs) some over-zealous architects
will do something dumb like have an order items table. In that table
you'd normally have a FK for orderID... and for the itemID (for each
item)... but you probably wouldn't need a customerID in that table,
etc... )

Moral of the story. More is usually better. Just don't over do it.

--Mike

ha**********@yahoo.com (H Cohen) wrote in message news:<15**************************@posting.google. com>...

Hi,

I have a corporate database with about 60 different tables that spans
manufacturing, accounting, marketing, etc.

It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields like customer_id
or product_id.

But should I do that?

My question is: Is there such a thing as too many relationships? Can
I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected and simply use
JOINS in my business code?

Thanks,
HC