Question about database structure - best practice

Report · Oct 31, 2007

I want to create, display, and maintain a data table of loan rates. These
rates will be for two loan categories - Conforming and Jumbo. They will be
for two loan terms - 15year and 30year. Within each term, there will be a
display of -

points (0, 1, 3) - rate - APR

For example -

CONFORMING
30 year
POINTS RATE APR
----------- --------- ------
0 6.375 6.6
1 6.125 6.24
3 6.0 6.12

My first question is -

Would it be better to set up the database with 5 fields (category, term,
points, rate, apr), or 13 fields (category, 30_zeropointRate,
30_onepointRate, 30_threepointRate, 30_zeropointAPR, 30_onepointAPR,
30_threepointAPR, 15_zeropointRate, 15_onepointRate, 15_threepointRate,
15_zeropointAPR, 15_onepointAPR, 15_threepointAPR)?

The latter option would mean that my table would only contain two records -
one for each of the two categories. It seems simpler to manage in that
regard.

Any thoughts, suggestions, recommendations?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

Report · Oct 31, 2007

> Any thoughts, suggestions, recommendations?

The answer is usually based on if you ever need to change part of the
records en masse, or if each record is an item unto itself.

If the former, then most would argue that you need to do more normalization
and have separate tables. But, you also have to weigh it against pragmatic
common sense.

-Darrel

Report · Oct 31, 2007

Thanks, darrel. I'm not so worried about optimal normalization, since this
table is largely a static element that will be changed manually by the
client as they see fit.

I'm thinking more about the display of the data - If I do it the first way
listed, then I will have to do a fair amount of manipulation to collate
category with data so as to display it in a combined table. The other way
listed makes that job easier, since there are only the two records.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"darrel" <notreal@nowhere.com> wrote in message
news:fga0kh$f18$1@forums.macromedia.com...
>> Any thoughts, suggestions, recommendations?
>
> The answer is usually based on if you ever need to change part of the
> records en masse, or if each record is an item unto itself.
>
> If the former, then most would argue that you need to do more
> normalization and have separate tables. But, you also have to weigh it
> against pragmatic common sense.
>
> -Darrel
>

Report · Oct 31, 2007

> Thanks, darrel. I'm not so worried about optimal normalization, since
> this
> table is largely a static element that will be changed manually by the
> client as they see fit.

I agree. I'd consider this more of a 'configuration settings' type of thing.
It just happens to be in a DB.

> I'm thinking more about the display of the data - If I do it the first way
> listed, then I will have to do a fair amount of manipulation to collate
> category with data so as to display it in a combined table. The other way
> listed makes that job easier, since there are only the two records.

I'd take the easy route. ;)

-Darrel

Report · Oct 31, 2007

> I'm thinking more about the display of the data - If I do it the first way
> listed, then I will have to do a fair amount of manipulation to collate
> category with data so as to display it in a combined table. The other way
> listed makes that job easier, since there are only the two records.

Also, how is this data shown/used on the site? Is it just shown as a table?
If so, another option might be to just create an HTML field in the DB and
have them edit an actual HTML table in an HTML editor on the page.

Obviously that wouldn't work if other tables in the DB rely on this data,
but if it's mosly just data that's shown as content on a page or two, that
might be the easiest option.

-Darrel

Report · Oct 31, 2007

Oh - interesting idea. I wonder if I could use ContentSeed for that?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"darrel" <notreal@nowhere.com> wrote in message
news:fga385$ig2$1@forums.macromedia.com...
>> I'm thinking more about the display of the data - If I do it the first
>> way listed, then I will have to do a fair amount of manipulation to
>> collate category with data so as to display it in a combined table. The
>> other way listed makes that job easier, since there are only the two
>> records.
>
> Also, how is this data shown/used on the site? Is it just shown as a
> table? If so, another option might be to just create an HTML field in the
> DB and have them edit an actual HTML table in an HTML editor on the page.
>
> Obviously that wouldn't work if other tables in the DB rely on this data,
> but if it's mosly just data that's shown as content on a page or two, that
> might be the easiest option.
>
> -Darrel
>

Report · Oct 31, 2007

I guess a better way to word my suggestion is: "Is this data used by the web
application, or is this relatively static information that just needs to be
editable and displayed on the site?"

If the latter, any text editor like TinyMCE would work to manage a TEXT
field in the DB to store HTML.

-Darrel

Report · Oct 31, 2007

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:fg9tev$at9$1@forums.macromedia.com...
> I want to create, display, and maintain a data table of loan rates. These
> rates will be for two loan categories - Conforming and Jumbo. They will
> be for two loan terms - 15year and 30year. Within each term, there will
> be a display of -
>
> points (0, 1, 3) - rate - APR
>
> For example -
>
> CONFORMING
> 30 year
> POINTS RATE APR
> ----------- --------- ------
> 0 6.375 6.6
> 1 6.125 6.24
> 3 6.0 6.12
>
> My first question is -
>
> Would it be better to set up the database with 5 fields (category, term,
> points, rate, apr), or 13 fields (category, 30_zeropointRate,
> 30_onepointRate, 30_threepointRate, 30_zeropointAPR, 30_onepointAPR,
> 30_threepointAPR, 15_zeropointRate, 15_onepointRate, 15_threepointRate,
> 15_zeropointAPR, 15_onepointAPR, 15_threepointAPR)?
>
> The latter option would mean that my table would only contain two
> records - one for each of the two categories. It seems simpler to manage
> in that regard.
>
> Any thoughts, suggestions, recommendations?

In my opinion, normalizing is not necessary with small sites, for example
the uber-normalized database design I did for the telcost compare matrix (
http://www.artronics.nl/telcostmatrix/matrix.php ) proved to be totally
overkill.

Joris

Report · Oct 31, 2007

Yeah - that's my thinking, too, Joris. Last thing I need right now is to
swat this fly with a sledgehammer!

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Joris van Lier" <whizzrd@hotmail.com> wrote in message
news:fgaart$sge$1@forums.macromedia.com...
>
>
> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
> news:fg9tev$at9$1@forums.macromedia.com...
>> I want to create, display, and maintain a data table of loan rates.
>> These rates will be for two loan categories - Conforming and Jumbo. They
>> will be for two loan terms - 15year and 30year. Within each term, there
>> will be a display of -
>>
>> points (0, 1, 3) - rate - APR
>>
>> For example -
>>
>> CONFORMING
>> 30 year
>> POINTS RATE APR
>> ----------- --------- ------
>> 0 6.375 6.6
>> 1 6.125 6.24
>> 3 6.0 6.12
>>
>> My first question is -
>>
>> Would it be better to set up the database with 5 fields (category, term,
>> points, rate, apr), or 13 fields (category, 30_zeropointRate,
>> 30_onepointRate, 30_threepointRate, 30_zeropointAPR, 30_onepointAPR,
>> 30_threepointAPR, 15_zeropointRate, 15_onepointRate, 15_threepointRate,
>> 15_zeropointAPR, 15_onepointAPR, 15_threepointAPR)?
>>
>> The latter option would mean that my table would only contain two
>> records - one for each of the two categories. It seems simpler to manage
>> in that regard.
>>
>> Any thoughts, suggestions, recommendations?
>
> In my opinion, normalizing is not necessary with small sites, for example
> the uber-normalized database design I did for the telcost compare matrix
> ( http://www.artronics.nl/telcostmatrix/matrix.php ) proved to be totally
> overkill.
>
> Joris

Report · Oct 31, 2007

Thanks, darrel. I'll look into that....

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"darrel" <notreal@nowhere.com> wrote in message
news:fgaah0$ru7$1@forums.macromedia.com...
>
>
> I guess a better way to word my suggestion is: "Is this data used by the
> web application, or is this relatively static information that just needs
> to be editable and displayed on the site?"
>
> If the latter, any text editor like TinyMCE would work to manage a TEXT
> field in the DB to store HTML.
>
> -Darrel
>

Report · Oct 31, 2007

But if the site ever wants to expand on it's functionality etc. it can be
very difficult to get round a de-normalised database. You can find that you
have tied yourself in knots and the only solution is to go back and redesign
the database which often includes major redesigning of the fron-end too.

If you are confident that this will not be the case then go with your
initial thoughts but don't be too lenient just in case. Leave yorself a
little scope. I always aim for 3rd normal form as this guarantees a robust
database design without being OTT.

Pat.

"Joris van Lier" <whizzrd@hotmail.com> wrote in message
news:fgaart$sge$1@forums.macromedia.com...
>
>
> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
> news:fg9tev$at9$1@forums.macromedia.com...
>> I want to create, display, and maintain a data table of loan rates.
>> These rates will be for two loan categories - Conforming and Jumbo. They
>> will be for two loan terms - 15year and 30year. Within each term, there
>> will be a display of -
>>
>> points (0, 1, 3) - rate - APR
>>
>> For example -
>>
>> CONFORMING
>> 30 year
>> POINTS RATE APR
>> ----------- --------- ------
>> 0 6.375 6.6
>> 1 6.125 6.24
>> 3 6.0 6.12
>>
>> My first question is -
>>
>> Would it be better to set up the database with 5 fields (category, term,
>> points, rate, apr), or 13 fields (category, 30_zeropointRate,
>> 30_onepointRate, 30_threepointRate, 30_zeropointAPR, 30_onepointAPR,
>> 30_threepointAPR, 15_zeropointRate, 15_onepointRate, 15_threepointRate,
>> 15_zeropointAPR, 15_onepointAPR, 15_threepointAPR)?
>>
>> The latter option would mean that my table would only contain two
>> records - one for each of the two categories. It seems simpler to manage
>> in that regard.
>>
>> Any thoughts, suggestions, recommendations?
>
> In my opinion, normalizing is not necessary with small sites, for example
> the uber-normalized database design I did for the telcost compare matrix
> ( http://www.artronics.nl/telcostmatrix/matrix.php ) proved to be totally
> overkill.
>
> Joris

Report · Oct 31, 2007

Thanks, Pat. I'm pretty sure that this is a dead-end expansion. The site
itself will surely expand, but I think this particular need will be
informational only....

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Pat Shaw" <pat@nomail.com> wrote in message
news:fgahav$7ms$1@forums.macromedia.com...
> But if the site ever wants to expand on it's functionality etc. it can be
> very difficult to get round a de-normalised database. You can find that
> you have tied yourself in knots and the only solution is to go back and
> redesign the database which often includes major redesigning of the
> fron-end too.
>
> If you are confident that this will not be the case then go with your
> initial thoughts but don't be too lenient just in case. Leave yorself a
> little scope. I always aim for 3rd normal form as this guarantees a robust
> database design without being OTT.
>
> Pat.
>
>
> "Joris van Lier" <whizzrd@hotmail.com> wrote in message
> news:fgaart$sge$1@forums.macromedia.com...
>>
>>
>> "Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
>> news:fg9tev$at9$1@forums.macromedia.com...
>>> I want to create, display, and maintain a data table of loan rates.
>>> These rates will be for two loan categories - Conforming and Jumbo.
>>> They will be for two loan terms - 15year and 30year. Within each term,
>>> there will be a display of -
>>>
>>> points (0, 1, 3) - rate - APR
>>>
>>> For example -
>>>
>>> CONFORMING
>>> 30 year
>>> POINTS RATE APR
>>> ----------- --------- ------
>>> 0 6.375 6.6
>>> 1 6.125 6.24
>>> 3 6.0 6.12
>>>
>>> My first question is -
>>>
>>> Would it be better to set up the database with 5 fields (category, term,
>>> points, rate, apr), or 13 fields (category, 30_zeropointRate,
>>> 30_onepointRate, 30_threepointRate, 30_zeropointAPR, 30_onepointAPR,
>>> 30_threepointAPR, 15_zeropointRate, 15_onepointRate, 15_threepointRate,
>>> 15_zeropointAPR, 15_onepointAPR, 15_threepointAPR)?
>>>
>>> The latter option would mean that my table would only contain two
>>> records - one for each of the two categories. It seems simpler to
>>> manage in that regard.
>>>
>>> Any thoughts, suggestions, recommendations?
>>
>> In my opinion, normalizing is not necessary with small sites, for example
>> the uber-normalized database design I did for the telcost compare matrix
>> ( http://www.artronics.nl/telcostmatrix/matrix.php ) proved to be totally
>> overkill.
>>
>> Joris
>
>

Report · Oct 31, 2007

.oO(Joris van Lier)

>In my opinion, normalizing is not necessary with small sites, for example
>the uber-normalized database design I did for the telcost compare matrix (
> http://www.artronics.nl/telcostmatrix/matrix.php ) proved to be totally
>overkill.

IMHO even for small sites normalization has its benefits. Of course
there's always a way to over-normalize, which will cause new problems
instead of solving them. But no normalization at all is bad as well.

It might be enough and working for now, but the problems will come if
one day the data has to be modified or the project around the DB changes
its requirements. An improper design will _always_ lead to problems on
the long run.

Micha

Report · Oct 31, 2007

.oO(Murray *ACE*)

>Thanks, darrel. I'm not so worried about optimal normalization, since this
>table is largely a static element that will be changed manually by the
>client as they see fit.

I would still prefer the first version. The second is not only a really
bad design, it also hurts the eye of every DB designer. ;)

>I'm thinking more about the display of the data - If I do it the first way
>listed, then I will have to do a fair amount of manipulation to collate
>category with data so as to display it in a combined table.

Shouldn't be that difficult. You can easily get all the data you need
(with some grouping functions) and for the script it shouldn't be that
difficult to display it. What system (programming language, DB) is this
supposed to run on?

>The other way
>listed makes that job easier, since there are only the two records.

The number of records doesn't really matter. The question is what you
want to do with the data and in which format your script expects it.

Micha

Report · Oct 31, 2007

> What system (programming language, DB) is this
> supposed to run on?

LAMP.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Michael Fesser" <netizen@gmx.de> wrote in message
news:vokhi3lcfkvce2mbvt2iqkktaes6rbdotf@4ax.com...
> .oO(Murray *ACE*)
>
>>Thanks, darrel. I'm not so worried about optimal normalization, since
>>this
>>table is largely a static element that will be changed manually by the
>>client as they see fit.
>
> I would still prefer the first version. The second is not only a really
> bad design, it also hurts the eye of every DB designer. ;)
>
>>I'm thinking more about the display of the data - If I do it the first way
>>listed, then I will have to do a fair amount of manipulation to collate
>>category with data so as to display it in a combined table.
>
> Shouldn't be that difficult. You can easily get all the data you need
> (with some grouping functions) and for the script it shouldn't be that
> difficult to display it. What system (programming language, DB) is this
> supposed to run on?
>
>>The other way
>>listed makes that job easier, since there are only the two records.
>
> The number of records doesn't really matter. The question is what you
> want to do with the data and in which format your script expects it.
>
> Micha

Report · Oct 31, 2007

.oO(Murray *ACE*)

>> What system (programming language, DB) is this
>> supposed to run on?
>
>LAMP.

Good.

And how do you want the data be displayed on the page? All in a single
table or splitted across several ones? Was the table from your first
posting an actual example?

Micha

Report · Oct 31, 2007

> Was the table from your first
> posting an actual example?

Yes, it was.

CONFORMING
JUMBO
30 year 15 year
30 year 15 year
POINTS RATE APR POINTS RATE APR POINTS RATE
APR ....

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Michael Fesser" <netizen@gmx.de> wrote in message
news:57phi3p7u1h1tju2hg23kjhkib3rs262vb@4ax.com...
> .oO(Murray *ACE*)
>
>>> What system (programming language, DB) is this
>>> supposed to run on?
>>
>>LAMP.
>
> Good.
>
> And how do you want the data be displayed on the page? All in a single
> table or splitted across several ones? Was the table from your first
> posting an actual example?
>
> Micha

Report · Oct 31, 2007

.oO(Murray *ACE*)

>> Was the table from your first
>> posting an actual example?
>
>Yes, it was.
>
>CONFORMING
>JUMBO
> 30 year 15 year
>30 year 15 year
>POINTS RATE APR POINTS RATE APR POINTS RATE
>APR ....

OK (even if it's a bit mangled because of the line breaks).

But then I don't really understand your comment that the first way (the
5-col table or something like that) would make it more complicated than
the second. For me the first one would probably be easier, because it
should allow for some loops to print the data out. In the second way you
would have to hand-code every single cell of the HTML table to fill the
values in, because the column names make it hard to loop through in any
way.

But that's just a quick thought, I would have to think about this more
deeply ... ;)

The reason why I asked for the platform was that there are some nice
functions in MySQL, that might come in handy in such situations like
yours, but I don't know if they are part of the SQL standard. For
example there is GROUP_CONCAT(), which can concatenate multiple values
from a group into a single string with a user-defined separator. Then
the receiving script doesn't have to fetch multiple records, but just
one and then split the received string. In some cases this can be quite
helpful.

Micha

Report · Nov 01, 2007

Hmm - I'll check the GROUP_CONCAT() function in my MySQL book - thanks for
the tip!

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Michael Fesser" <netizen@gmx.de> wrote in message
news:9fqhi3187pfan240n2evc1pib1o6tcdhrs@4ax.com...
> .oO(Murray *ACE*)
>
>>> Was the table from your first
>>> posting an actual example?
>>
>>Yes, it was.
>>
>>CONFORMING
>>JUMBO
>> 30 year 15 year
>>30 year 15 year
>>POINTS RATE APR POINTS RATE APR POINTS
>>RATE
>>APR ....
>
> OK (even if it's a bit mangled because of the line breaks).
>
> But then I don't really understand your comment that the first way (the
> 5-col table or something like that) would make it more complicated than
> the second. For me the first one would probably be easier, because it
> should allow for some loops to print the data out. In the second way you
> would have to hand-code every single cell of the HTML table to fill the
> values in, because the column names make it hard to loop through in any
> way.
>
> But that's just a quick thought, I would have to think about this more
> deeply ... ;)
>
> The reason why I asked for the platform was that there are some nice
> functions in MySQL, that might come in handy in such situations like
> yours, but I don't know if they are part of the SQL standard. For
> example there is GROUP_CONCAT(), which can concatenate multiple values
> from a group into a single string with a user-defined separator. Then
> the receiving script doesn't have to fetch multiple records, but just
> one and then split the received string. In some cases this can be quite
> helpful.
>
> Micha

Report · Nov 01, 2007

I do not see GROUP_CONCAT() in my MySQL reference, although I do see it
here -

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"Murray *ACE*" <forums@HAHAgreat-web-sights.com> wrote in message
news:fgcctk$nm0$1@forums.macromedia.com...
> Hmm - I'll check the GROUP_CONCAT() function in my MySQL book - thanks for
> the tip!
>
> --
> Murray --- ICQ 71997575
> Adobe Community Expert
> (If you *MUST* email me, don't LAUGH when you do so!)
> ==================
> http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
> http://www.dwfaq.com - DW FAQs, Tutorials & Resources
> ==================
>
>
> "Michael Fesser" <netizen@gmx.de> wrote in message
> news:9fqhi3187pfan240n2evc1pib1o6tcdhrs@4ax.com...
>> .oO(Murray *ACE*)
>>
>>>> Was the table from your first
>>>> posting an actual example?
>>>
>>>Yes, it was.
>>>
>>>CONFORMING
>>>JUMBO
>>> 30 year 15 year
>>>30 year 15 year
>>>POINTS RATE APR POINTS RATE APR POINTS RATE
>>>APR ....
>>
>> OK (even if it's a bit mangled because of the line breaks).
>>
>> But then I don't really understand your comment that the first way (the
>> 5-col table or something like that) would make it more complicated than
>> the second. For me the first one would probably be easier, because it
>> should allow for some loops to print the data out. In the second way you
>> would have to hand-code every single cell of the HTML table to fill the
>> values in, because the column names make it hard to loop through in any
>> way.
>>
>> But that's just a quick thought, I would have to think about this more
>> deeply ... ;)
>>
>> The reason why I asked for the platform was that there are some nice
>> functions in MySQL, that might come in handy in such situations like
>> yours, but I don't know if they are part of the SQL standard. For
>> example there is GROUP_CONCAT(), which can concatenate multiple values
>> from a group into a single string with a user-defined separator. Then
>> the receiving script doesn't have to fetch multiple records, but just
>> one and then split the received string. In some cases this can be quite
>> helpful.
>>
>> Micha
>

Report · Nov 01, 2007

Murray *ACE* wrote:
> I do not see GROUP_CONCAT() in my MySQL reference

AFAIK, it's available in MySQL 4.1 and above.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Nov 01, 2007

Yeah - I did see it on the MySQL website.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================

"David Powers" <david@example.com> wrote in message
news:fgcv6c$he6$1@forums.macromedia.com...
> Murray *ACE* wrote:
>> I do not see GROUP_CONCAT() in my MySQL reference
>
> AFAIK, it's available in MySQL 4.1 and above.
>
> --
> David Powers, Adobe Community Expert
> Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/