If ur storing "ALPHANUMERIC" values, then use varchar instead of TEXT
On 7/11/08, Waynn Lue <[EMAIL PROTECTED]> wrote:
>
>
>
> 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
>
