Hello all,
Thank-you for all of your help, I was really surprised by the speed &
quality of responses. Below is a table I've created based on some reading I
did following everyone's suggestions (I hope the table shows correctly)...
I'm leaning towards the pure Relational as I like having all data in a
singular, easily conceptualized & exported datastore but we may go with the
ENUM approach if it provides a performance boost. Feel free to comment...
Relational
Enum
Scripted
Maintainability
+
(Simplest, known in industry, other programmers can easily
understand/maintain)
+
(Almost as simple as Relational, known in industry, other programmers
can easily understand/maintain)
-
(Will have 2 datastores, the SQL DB & XML or hard-coded values; NOT
complicated, but not as intuitively maintainable)
Scalability
+
(Easily Scalable, simplified by the fact there's 1 datastore. Adding
a State as easy as adding record to db, no code changes needed!)
+
(Easily Scalable, simplified by the fact there's 1 datastore. Adding
a State as easy as adding new ENUM to db, no code changes needed!)
N/-
(Not as Easily scalable, adding/removing field means changing code
which always has risks; of course done right: we'd just add new code to an
XML file)
Code Complexity
+
(Super simple, easy to understand, GET DATA:connect, query, show;
WRITE DATA validate, connect, write)
+
(Super simple, easy to understand, GET DATA:connect, query, show;
WRITE DATA validate, connect, write)
-
(Alittle more involved
GET DATA: connect, query, turn codes into words via code, showd
WRITE DATA: similar to Relational/Enum
Performance
N or -
N or -
+
Could potentially increase performance by taking load off SQL server.
Portability (Using w/ 3rd party System or future external application)
+
Integrating w/ 3rd party SW or new applications simple.
+ (?)
Integrating w/ 3rd party SW or new applications probably like
Relational. Are ENUMS handled/returned via normal SELECT queries? (if so,
just like relational)
-
Would have to write the decodify module for any outside system. Isn't
intuitively portable.
ThanX,
Ben
""David Ashley"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Tue, Sep 23, 2008 at 11:13 AM, Olexandr Melnyk <[EMAIL PROTECTED]>
> wrote:
>
>> On 9/23/08, David Ashley <[EMAIL PROTECTED]> wrote:
>>>
>>> For example, for the 50 states, a lot of programmers would put this
>>> logic
>>> in
>>> the web script and just store the two-letter postal code in the database
>>> table (but with no separate table for "states"). The mapping from "MI"
>>> to
>>> "Michigan" would occur in the web scripts; as would the logic to know
>>> that
>>> "XQ" is not a state.
>>>
>>
>> Keep in mind that, as you add more countries, maintaining in-scipt
>> code/name mappings of state codes will become more and more cumbersome.
>> Another problem will arise if you want to view user information, along
>> with
>> full country and state names, from a different programming language.
>>
>> Plus, if the same query is run very often and table is almost static,
>> chances are high that the result will be in query cache.
>
>
> The "different programming language" issue is one I hadn't thought about.
> Good catch.
>
> I program in PHP only, and I run queries manually only if I need to debug
> something hard. But I could see the issue you cited arising in a more
> complex environment. I could even see a "mixed" web application where the
> web presence is a mixture of PHP, Python, and Perl.
>
> Good catch.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
