I feel creating the third table would just be duplicating the data and occupying space. Creating index on existing table and quering would be better. But you got a good point of FOREIGN KEY CONSTRAINT. Can we use this constraint on current "SETTINGS" table, rather than creating the third table.
On 7/7/08, metastable <[EMAIL PROTECTED]> wrote: > > Waynn Lue wrote: > >> I have two tables, Applications and Settings. Here are the two schemas: >> >> mysql> desc Applications; >> >> +-----------------+---------------------+------+-----+---------+----------------+ >> | Field | Type | Null | Key | Default | >> Extra | >> >> +-----------------+---------------------+------+-----+---------+----------------+ >> | ApplicationId | bigint(20) unsigned | | PRI | NULL | >> auto_increment | >> | ApplicationName | varchar(200) | YES | | NULL >> | | >> >> +-----------------+---------------------+------+-----+---------+---------------- >> >> mysql> desc Settings; >> +---------------+---------------------+------+-----+---------+-------+ >> | Field | Type | Null | Key | Default | Extra | >> +---------------+---------------------+------+-----+---------+-------+ >> | ApplicationId | bigint(20) unsigned | | PRI | 0 | | >> | SettingId | int(10) unsigned | | PRI | 0 | | >> | SettingValue | text | YES | | NULL | | >> +---------------+---------------------+------+-----+---------+-------+ >> >> Settings is just a multi map, where I'll have things like (1, 1, "true"), >> (1, 2, "short text"), (1, 3, "145"), etc. Essentially each Application >> has >> multiple rows in Settings. >> >> There are values in Settings that I want to index, for example I want to >> find all ApplicationIds where SettingId = 34 and SettingValue = "149951". >> Here are the two methods I'm considering. >> >> 1. Add an index on (SettingId, SettingValue). It allows me to look for >> any >> arbitrary combination of SettingId, SettingValue, and it also gives me >> flexibility in that any thing I add to the table, it'll automatically be >> indexed. Problem is that there are combinations that I'll never care >> about, >> so I'm wasting space and INSERT time. >> 2. Pull the values that I'm interested in out into Applications, and add >> it >> as a column that that table with a secondary index. I'll have to update >> both Applications and Settings in a transaction, and make sure they're >> kept >> consistent. This is more efficient, but every time I want to index >> something new, I'll have to either take down Applications and add a new >> column, or I'll have to create another table and INNER JOIN it to >> Applications. >> >> Does anyone have experience building out a system that's similar? Am I >> getting all the pros and cons? Also, is a VARCHAR better than a text >> field >> in terms of indexing, or even in terms of performance? >> >> Thanks for any input, >> Waynn >> >> >> > If you query the application id and then carry it around in your > application, and if InnoDB is not an objection, why not use foreign key > constraints ? > This would index your columns, without you having to worry about it, albeit > with a slight performance disadvantage, but hey, it's the 21st century. > > TABLE applications (applicationid PK, applicationname UNIQUE) > TABLE settingtypes (settingid PK, settingdescription VARCHAR?) > TABLE settings(applicationid FOREIGN KEY, settingid FOREIGN KEY, setting > VARCHAR, UNIQUE KEY(applicationid, settingid)) > > > HTH. > > Stijn > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
