Database design question - resource network

#11

Hi everybody,
First of all thank you for the advice.
As I said before, I'm consciuos that trying to model these
requirements with a relational db means I'm probably using the worng
tool (or stretching it at least).. and I appreciate the advice both
from Neo and Ed to start looking into some different kind of tools..
Unfortunately, the db I'm tring to model is just a really small and
insignificant (in relation with my objectives) part of the project I'm
working on.
ie: Given the "theoretical" issues related to the subject, I'll just
settle for a maybe "not so elegant" compromise. And -- really -- an
"entity" table with sub/super tables as suggested by Ed, sounds just
peeerfect.

Thanks,
Marco

On 5 Feb, 20:52, "Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Feb 3, 9:17 am, "Walt" <wami... (AT) verizon (DOT) net> wrote:

"Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote in message

news:1170439341.306920.144360 (AT) k78g2000cwa (DOT) googlegroups.com...

On Feb 2, 12:09 pm, "AlterEgo" <altereg... (AT) dslextreme (DOT) com> wrote:
Marco,

This is one way to accomplish what you want.

EntityType table
------
EntityTypeID PK
EntityTypeName

Entity table
------
EntityID PK
EntityTypeID FK to EntityType table
EntityName

AffiliateType table
------
AffiliateType PK
AffiliateTypeName

EntityAffiliatedWithEntity table
------
EntityID PK, FK to Entity table
AffiliateEntityID PK, FK to Entity table
AffiliateTypeID FK to AffiliateType table.

EntityTypeName could be Person, Book, Conference, etc.
AffiliateTypeName could be "Writes", "Edits", "Publishes", etc.

-- Bill

have you been taking DB design lessons from neo?

this looks like a variation of EAV. Only problem is you left out the
Value portion.

ed

Ed: PMFJI.

The OP is a classic question on how to design a database. The same question
gets asked over and over again in every database forum. The subject matter
varies from bibliographies to software libraries to parts lists to
cataloguing the genome. But it's really the same question, if you can
recognize it as such.

The generic question is: I want my users to be able to discover and record
new data relationships, using only DML. I want the system to be able to
react to these new discoveries as if the new data relationships had been
recorded using DDL. I don't want my users doing DDL, but I need a database
structure that will be invariant to the discovery of new data relationships.

What that means is that (some of) what looks like data to the DBMS is really
metadata. And the DBMS metadata is really what: meta-metadata?

EAV is a classic solution to this conundrum. It has a whole lot of defects,
which I'm sure I don't need to tell you about. But almost every discussion
of meta-metadata will inevitably be drawn to a discussion of EAV.- Hide quoted text -

- Show quoted text -

Sounds like this topic should move to comp.databases.theory. The sad
thing is, marco is tasked with designing the database without knowing
database theory. Kind of like trying to design a high efficiency car
engine without knowing the Carnot Cycle.

But if he is using a Relational DBMS, then it is just a question of
deciding on the level of abstraction to be modeled. The model is the
data and the DBMS metadata is just the metadata.

And actually, marco may be totally in the wrong area. If the goal is
to build a system that expands relationships, then maybe he should
look to those kinds of pattern processing systems. AI type systems
come to mind.

Well, we certainly gave him enough to think about!
HTH,
ed

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Database design question - resource network

comp.databases comp.databases

Re: Database design question - resource network - 02-09-2007 , 04:52 AM