> I am time and again asked to build a cross join table with > 2-3 primary keys. > Also i am asked time and again to build a table that doesn't > use a auto incrementing Primary key. > > This is usually to port an antiquated system to SQL and web > enable it (think AS400). > > Has anyone built a Crud system that can handle either of > these situations? > > If not what would you do to create the insert query with > Catch/Throw to create the following seneraios? > > Currently I have a client list for a company where the person > is uniqued by: > *select CityCode + '-' + Left(FirstName,1) + '-' + > Left(LastName,1) + '-' + > Right(Social,4) Or IND-RD-xxxx* > All items are in the form and concatinated together in the > action page. > In another table I have a cross join of ClientID, ProviderID, > ServiceID all primary keys each of the 3 items can be picked > from the form in drop downs. > > Just curious how others might handle these...
I would create a surrogate primary key on each table, as Tom suggested, and add a uniqueness constraint to the set of fields you currently use as the primary key in each table. The uniqueness constraint will prevent duplicates, and you could then use any CRUD system you like. You could then rewrite the joins in your current system to use the surrogate key - you'd obviously have to migrate the primary key to each related table as a foreign key, of course. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296548 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
