[thelist] MySQL database optimis(z)ation

[Warden, Matt]

On Feb 4, rudy had something to say about Re: [thelist] MySQL database...

>> which explains why rudy wasn't immediately familiar with it:
>> he's never had to be at the mercy of a DBA
>
>on the contrary, i started out as one,

i seem to recall you saying things like "I'm not a DBA, but..."

sorry about that.

>i fell in love with your examples (CATNAME, ICQNUMBER, AIMNAME) because
>they seemed to be adapted from the examples i always used in explaining the
>tables i designed two years ago to support evolt's member page data

yeah, i stole those... but only because naming other examples from my
former employer would conjur horrible memories of my introduction to the
structure (it took me a good week or two to get it to click). and, boy,
they were everywhere. in fact, we had a generalized general attributes
structure (heh). We had a general ATTRIBUTE table which had a foreign key
to the ATTRIBUTETYPE table which would have values like:

ATTRIBUTETYPE
-------------------
ID	| Name
-------------------
1	| Member
2	| Site
3	| Order
4	| Product

and then ATTRIBUTE would look like:

ATTRIBUTE
----------------------------------------------------------------------
ID	| AttributeTypeID	| Name		| AttributeDataTypeID
----------------------------------------------------------------------
1	| 1			| CATNAME	| 1
2	| 1			| ICQNUMBER	| 2
3	| 2			| FONTCOLOR	| 1
4	| 2			| LOGO		| 1
5	| 4			| SIZE		| 1

and then we'd have an ATTRIBUTEVALUE table:

ATTRIBUTEVALUE
--------------------------------
ID	| AttributeID	| Value
--------------------------------
1	| 5		| XL
2	| 5		| S
3	| 3		| #cc33ff


i think that was it, anyways. it's been quite a while since i was working
on the product that had this complex structure (oh, the joins, the joins!)


>those tables are still there, waiting for the member pages project
>
>[kicking myself for not being more proactive on that project]

[kicking you for not being more proactive on that project]

heh. there are issues we need to resolve before getting into that, right?

>if i came across as unfamiliar with generic structures in my reply to kris,
>then i have some more work to do it improving my writing abilities

I apologize for misreading you. I shoulda known better :)

>my battles with DBAs notwithstanding -- and i've had plenty of them -- your
>examples were still very illuminating

thanks rude.

>p.s.
>
>i believe -- no, scratch that, i know, i gots it right here in my mail
>folder -- that i said "it's hard to see how it works (i *think* i finally
>got it)"
>
>what i meant was, i looked at it to see that it fit the generic model
>
>i've seen attempts at generic structures that failed, so it's not always
>the easiest thing for people to get right, and in a way that it doesn't bog
>things down (semantically and in performance)

like i said, i shoulda known better.

<insert stuff about how you should write an article on the subject />

<prod tool="iron rod" />
<prod tool="iron rod" />
<prod tool="iron rod" />


--
mattwarden
mattwarden.com