My remarks are interspersed below for reasons which will probably be obvious.
Rhino ----- Original Message ----- From: [EMAIL PROTECTED] To: Stuart Felenstein Cc: [EMAIL PROTECTED] ; Peter Brawley ; Rhino Sent: Friday, August 27, 2004 12:33 PM Subject: Re: Many to Many: Does this make sense ? Stuart, Both Rhino and I have suggested the same basic data storage structure to you. What you need to understand is that your RAD tool is not supporting your application design. You can use a form with 5 or 10 blanks on it where the user can list all of their choices. Then when the user submits the multiple field input form, your handling code will need to go down the list of fields and make the appropriate database entries. 1 form with 5 fields. User is happy, database is happy. You have kept the user from entering too many choices because you only gave them as many fields as you wanted them to have. In this case, the application's user interface is enforcing a business rule through its design. Your data base doesn't care how many titles each member has (from 0 to several billion) but your application requirements need the user to have no more than 5. That's a business rule and you code for that above the database (either in your validation code or, in this case, through UI design) I'm fine with what you've said so far. I would stick with the many-to-many database design Really? Have you *ever* seen a true many-to-many relationship implemented in a real database? I don't recall seeing one in over 20 years of system work and some rather large databases. I've heard a lot of reasons why a pure many-to-many relationship is a very bad thing to implement and have always stayed clear of them myself. I'm very reluctant to suggest implementing a many-to-many, especially to a newbie who doesn't fully grasp the consequences of doing so.... as you don't want people mis-typing titles all of the time:"Godfather" is not "The Godfather" which is different than "The God Father" and wouldn't match "The Godfather I" ...(can you see where I am going?). Keep a separate table of titles, even if they are used only once or never at all. That way if anyone needs to look one up, you already have a list. You could also add titles to the list BEFORE any members wants to see them and you can make sure they are spelled correctly. I only trust users to type in things that the users are expert at and I don't think that your users are going to be a bunch of film historians. I would pre-enter what I could and review any new entries they make (nobody said your users can't add to the list, did they...). I think you're off on a bit of a tangent here. You're talking about validation edits here, not many-to-many relationships. I agree that it would probably be better to have users pick movies from an existing list rather than typing the names in themselves; the reasons you give are perfectly valid for doing so. But I don't see where this ties in directly to the question of implementing a many-to-many relationship. If I were building Stuart's system, I'd create the Titles table and put all of the rows in it before the user ever used the system; the user would be allowed to see the contents of the Titles table and then click on whichever movies they wanted, up to their limit of 5. That way, they couldn't request "Godfather", "The God Father", or some other movie that doesn't exist in the real world. Of course, you'd still need a mechanism to add movies to the list; you could go several different ways on that. If you were routinely adding most new releases to the Titles table already, you might get by with a simple request form where the user identifies a movie he'd like to see in the list and you eventually acquire it and add it to the list on his behalf. But if users were making a lot of requests, particularly for obscure titles, you might handle that differently. Let's set that issue aside for now though; it's off topic in my view. Let us know if any of this makes sense or not, please. This is an important topic in database design and you are not the first person, nor will you be the last, to make this design decision. The more we discuss this, the better off the next person with this problem will be. Agreed!! No one was born knowing database design and even experts can learn new tricks or new reasons for doing - or not doing - things. Newbies can learn a lot from experts. Experts even learn from newbies sometimes. So continuing to discuss important topics like this can benefit all concerned. No one wants to browbeat you into doing something you don't understand or believe in. I think everyone concerned wants you to do the right things for the right reasons and wants you to know why you are doing them so that you can justify it in your own mind. Rhino
