On Mon, Jul 7, 2008 at 4:20 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote:
> Yes, i definitly agree on the third table if "APPLICATION FUNCTIONALITY"
> increase and need to add more columns into the current tables.
>
> On 7/7/08, metastable <[EMAIL PROTECTED]> wrote:
>>
>> Ananda Kumar wrote:
>>
>>> 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.
>>>
>> [snip]
>>
>>
>>
>> On the contrary, using the third table would eliminate all data
>> duplication, thus safeguarding data integrity and making maintenance easier
>> (think 'I want to change this setting description or make it a boolean in
>> stead of a text setting'). It will also use less space because of the
>> previous.
>>
>> Ofcourse, I don't see any settings description or setting types
>> limitations in your schema as it is now.
>> If that isn't an objection, you could create the foreign key constraint on
>> the existing table as such:
>>
>> ALTER TABLE Settings ADD UNIQUE KEY (applicationID)
>> ALTER TABLE Settings ADD FOREIGN KEY (applicationID) REFERENCES
>> Applications(applicationID) ON UDPATE CASCADE ON DELETE SET NULL
>>
>> (from the top of my head, something like this)
>> This implies that you are using InnoDB tables !
>>
>> I would however still create the third table. It will make your life much
>> easier when at some time in the future you decide to expand the
>> functionality and do indeed include limitations on the settings ('must be
>> boolean', 'must be any of green,red,blue', ...), to allow for validation and
>> such.
>>
>>
>> HTH
>>
>> Stijn
>>
>>
> Well, the other thing I'm somewhat concerned about is that the column type
is TEXT. For some of the things I'm indexing, it's an INT, but I had to
create SettingValue as TEXT so that we could store text in it as well. It
seems like a waste to index on text when the underlying value is really a
number.
Waynn
