In your situation (or any situation, IMHO), multi-value fields defeat the purpose of good database design.
I think you are on the right track if you are willing to tolerate a little redundancy for the sake of simplicity (as a one-to-many relationship). If you really want to structure it as a many-to-many relationship, then try the following (I throw my 2 cents without knowing exactly what you are dealing with in terms of data structures): Table 1: list (entity table) listId, owner, date, ... Table 2: entry (entity table) entryId, entryDetails (attribute 1, 2, 3, ...)... Note: Lump all entries from different lists together and use an auto_increment primary key and a unique key constraint for every entry. Table 3: listEntry (relation table) listId, entryId Such as: A, entry10 B, entry20 B, entry65 C, entry65 D, entry50 Note: Make (listId, entryId) a primary key or at least a unique key. Hope this helps. Jack --- Ben Holness <[EMAIL PROTECTED]> wrote: > Hi Chris/Nick/Scalper, > > Thanks for the replies. I am not too sure how to > implement this in tables, > so I will give an example: > > Let's say I have three lists - a,b and c. List a > contains 10,000 entries, > list b contains 2,500 entries and list c contains > 75,000 entries. > > I have a table of lists, with list ID, owner and > date fields: > a,Ben,28/03/2002 > b,Ben,28/03/2002 > c,Tom,28/03/2002 > > Then I have another table for each list, with all of > the entries. The fields > would be list ID and entry: > > a,entry1 > a,entry2 > a,entry3 > ... > b,entry1 > b,entry2 > ... > c,entry1 > c,entry2 > c,entry3 > ... > > Have I understood the two table concept correctly? > How does the third table fit in? > > I guess that if two (or more) of the entries > overlap, I could make things > even better by having a multi-value field for the > list ID in the second > table, allowing: > > b&c,entry65 > > if entry65 was common to lists b and c ... Is there > a limit to the number of > values in a multi-value field? (Does MySQL offer > multivalue fields?) and > would this be appropriate? > > Cheers, > > Ben > > > > I would recommend: > > > > A table of lists (one row for each list with > owner, ID and > > creation Date > > (add modification date?)) > > > > A table of list entries (one row for each entry) > > A third table linking the list to list entry. > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list > archive) > > To request this thread, e-mail > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > __________________________________________________ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
