Tony, Albert, Larry & R:azzak: Thanks very much for the perspective and Razzak, for a welcome primer on key formation: never hurts to hear it.
I'm asking because I keep finding opportunities to get the logical results I need by using an M2M table as one of the parent tables in a relation; and whether one goes with the auto-numbered PK or the UNIQUE-indexed cluster of FKs, there's going to be a coding cost. I'm just wondering which bears the lowest complexity during coding, and which in the long run which approach is easiest to maintain and understand. Much appreciated, bruce chitiea safesectors inc > -------- Original Message -------- > Subject: [RBASE-L] - Re: Many-2-Many Table Primary Keys: > Recommendation? > From: Lawrence Lustig <[email protected]> > Date: Wed, July 13, 2011 10:37 am > To: [email protected] (RBASE-L Mailing List) > > > << > Or the make-one-up approach; which seems to have the dual advantages of > > allowing auto-numbering while providing a single PK value for > 'downstream' coding. > >> > > I'm a big advocate for autonumbered "non-intelligent" primary keys. But in > the case of a M-t-M table it's generally preferable to use the two existing > columns as the primary key. > > The main reason I don't like compound primary keys is that it helps to have a > single column identifier for situations like Variable Lookup Listviews which > only work correctly if each row can be uniquely identified through a single > column. > > In the case of a many-to-many table, however, it's almost always true that > you'll be looking at that data in the context of one of the two columns being > fixed. For instance, if your table joins Ingredients to Recipes, you'll > always be looking at the many-to-many data from either the Recipe form (in > which case IngredientID is sufficient to identify the rows in a listview) or > from the Ingredient form (in which case RecipeID is). > > Since you're going to need a unique constraint on the combination of columns > anyway, using them as the primary key means that you can kill two birds with > one index. > > -- > Larry
