[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
|
|