Brian Donahue on 24 Apr 2006 19:09:06 -0000


[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]

Re: [PhillyOnRails] Newbie Data Design Question


I think a typical way to approach this would be to have a "roles" table listing all the possible roles, a "people" table listing all people, and a "books" (or whatever you name it) table with all books.  Then you would have people_roles table which would contain:

person_id
book_id
role_id

A person could then have multiple roles (on the same book even) and it would allow for normalized data and more flexibility.

Hope this helps,

Brian




On 4/23/06, Walter Lee Davis <waltd@wdstudio.com> wrote:
I have a theory question for you older hands here.

Imagine a database of old books, each of which may have one or many
authors, editors, translators, co-editors, co-authors, etc., etc.
Furthermore, in the universe of people who have performed in these
various roles to the collection of books, it is likely that a person
has had more than one role. A translator of one title might also be
an author or an editor for a different title.

I don't want to have to ever re-enter the same person (and all their
metadata, like birth date, death date, and especially their bio) just
to give them a different role. I also don't want to maintain two
separate link tables and three data tables just to describe this many
to many to many relationship.

What I envision is having a role table, with a role name and two
foreign keys, one each for the person_id and the title_id. Any given
role would necessarily be entered more than once, but from an
interface and data standpoint, I am not so religious about
normalization that this bothers me much.

Are there any other structures that any of you can suggest?

Thanks,

Walter
_______________________________________________
talk mailing list
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk

_______________________________________________
talk mailing list
talk@phillyonrails.org
http://lists.phillyonrails.org/mailman/listinfo/talk