Hi everybody,
I'm a designer with some basic database knowledge but I would like
some suggestions on a database design problem that's really too much
for me..

Here's the question:
I need to build some kind of a bibliographical database for storing:
People,
Books,
Conferences,
Groups (universities, research groups,...),
Projects

I also want to be able to store any _arbitrary_ relationship between
any of them (many to many) for example:
- People may:
write/edit/publish/... books
partecipate/direct/organize/... conferences
be part of/direct/... groups
partecipate/guide/write about/... projects
- Books may:
cite/review/.. other books
be proceedings of/be presented at/... conferences
....

You get the idea. Any entity can have an arbitrary relationship (user
entered, not hard coded) with any other entity.

Now, for the solution I considered either making a mess with a
thousand many to many relationship tables:
table book_book (idRelationship, idBook1, idBook2, relationship_type)
But it makes everything really complicated and hard (for me) to
query..

Or, a thing I made up myself that I suppose is something a database
designer would abhor: some kind of superfreedom-many-to-many:
table relationship (idRelationship, id1, type1, id2, type2,
relationship_type)
for example: 23, person-> 15, book, (author)

I suppose I'm not the first one trying to use a db to store a network
structure.. do you have any suggestions or reference? It doesn't need
to be super-fast or super-scalable, but it does need to make sense..
The final aim is mainly to navigate and visualize relationships and
graph.

Hope you can help me.

Thank you,
Marco

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

"Marco Q." <marcoq (AT) gmail (DOT) com> wrote

Hi Bill,
thank you for your response, it sure makes a lot more sense than what
I thought of..
The only problem with your solution is that I actually need also to
describe the various entities, and they have different attributes.

For example for books I also need some "normal" columns describing
publishing date, number of pages, etc.
For person i need age, firstName, lastName, (...) columns. And the
same for the other entities.

So a "Entity" table for everything is not enough.. I believe i need a
"Book", "Person", "Conference", "Group" and "Project" table.
Hope there's a good solution for this too..

Thanks,
Marco


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

On Feb 2, 10:06 am, "Marco Q." <mar... (AT) gmail (DOT) com> wrote:
Good you identifed your entities

You started (but haven't finished) identifying your relationships.
Still good.

Why do you think they are "arbitrary"?

Note, if dsigned and implemented correctly you can relate entities in
a Relational DB that do not have explicit relation tables. For example
if you have a table of people, a table of books, a child table of
books namely authors (a book can have more than one author), and
another table of university faculty. then you might easily find all
the faculy that know each other because they have co-authored books.
You don't need a professor_worked_with relation table. IOW, you may be
making more work for yourself than you need.

Why is it so hard? Consider making views for the major queries you
need to make. Then it looks like fewer denormalized tables, when the
reality is lots of well normalized tables.

Cough. here we go, starting down the EAV path again.


You only have a network structure because you are not thinking about
the data properly. Those ID fields start to become network links and
you end up using your Relational model DB as a Network model one, only
without the tools of the old Network model DB products. (nothing to
help repair the IDs that link it all together when it gets out of
sync).

There is nothing wrong with a database with many tables. Some of them
you might later denormalize for performance. others you might wrap in
views. A helpfull iea in deciding on your final design is to consider
what queries you might run on the database. Also, some relations might
allow you to tunnel to connections that do not explicitly exist (which
is another way of saying you do not need to create relation tables
between EVRY pair of entities).

You have started down the golden path. Don't abandon it now.
Ed

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

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

ed

Hi Ed,
thank you for the insight. I'm glad I haven't messed up completely.
....but I must still make some clarifications.

The problem probably lies on the _arbitrary relationships_
The whole point of this PhD project I'm working on is the flexibility
of the structure. Now I know that structure flexibility doesn't play
well with data-design, but the final user really needs to be able to
specify _any_ kind of relationship between any entity. That means I
cannot hard-code in the data structure relationships like "works for",
because I want to be able to specify also "is in love with" and "plays
table tennis with".
I know this sounds silly, but that's the whole point of the project,
so trust me on that.. (Hey, it's an academic project, it doesn't need
to make too much sense..)

Now this sounds interesting. In my homemade database design studies I
never got to views (It was a big book..), do you think I could make my
"superfreedom-many-to-many table" with a view? I will need to look
into that.

Seems like you don't like this EAV thing (never heard of it)...
Is it that bad? Could you elaborate why? (Or give some reference?)
Who knows, maybe it works..

No really. I'm positive (99%) I have a network structure by design.
Should I look into some old Network model DB products? Now if I could
avoid that..
Isn't there a _newer_ Network model DB? Or something down that path..

This sounds like the path to follow..
Mostly my queries will be: select any entity that is linked to this
one, and all the links. (maybe down a couple of levels..)

Ed, you're really helpful. I know that what I'm trying to do probably
goes against good design theory (plan entities, relationshis..), but
that's inavoidable..

Ciao,
Marco

PS: Luckily it's a design phd, not a cs phd..

Ed,

No. Given the additional requirements, I would set up subordinate tables for
each entity in a supertype/subtype relationship. I've used similar
structures before where the same entity can have multiple roles and multiple
relationships and the permutations aren't known ahead of time.

-- Bill

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

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.

On Feb 3, 9:17 am, "Walt" <wami... (AT) verizon (DOT) net> wrote:
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

What you need is a database similar to dbd. If you would like to post
some sample or preferably actual data, I can populate a db for you to
browse/query. Someone else can post an RMDB solution. Note, dbd is an
experimental db based directly on set theory and lambda calculus.

None of the RMDB based solutions for your requirements is pretty. At
extremes, it can be done with just a few tables (using EAV-type
schema) or with many dedicated tables. Each extreme has advantages and
disadvantages and the best compromise will depend on your particular
requirements.

See examples at www.dbfordummies.com/example