From: "Harald Fuchs" > > id INT(11) - accountID > > name VARCHAR(32) - parameter name > > value INT(11) - parameter value > > > Other tables contain string, datetime, etc. parameters. > > > Since most searches are made for a value (or range) of one or more > > parameters, a usable primary key is: > > name-value-id > > That's a horrible denormalization. If one named parameter can hold > only one INT value for one account id, then (id, name) could be a > primary key; otherwise, you'd need a surrogate primary key.
And what if you want to find the IDs which have a certain value in a parameter? Then you're searching for name and value and only need the id as the glue for joins. The sitution is that I have to store a variable (and changing) number of parameters for a large number of IDs. The traditional construction of a column for each parameter is not usable anymore: - modifying the table structure for new parameters requires a lot of time - all unused parameters will still take space - design of index(es) is virtually impossible because searches are made on various combinations of parameters The parameter table solution is rather simple, but has some performance complications once you have 26,000,000 records for 475,000 accounts in a 5,3GB database... How would *you* normalize this situation? All suggestions are welcome! Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
