Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Attribute-values in separate table

 
Goto page 1, 2
   Database Forums (Home) -> Technology and Theory RSS
Next:  Access 97 to Access 2003 DataBase conversion  
Author Message
anjasmedts

External


Since: Aug 30, 2007
Posts: 3



(Msg. 1) Posted: Thu Aug 30, 2007 10:54 am
Post subject: Attribute-values in separate table
Archived from groups: comp>databases>theory (more info?)

Hi,

I have a to design a structure for a table with many attributes
containing Yes of No values. The program I write, has to react in
different ways corresponding the Yes/No-values in the different
attributes.
For many records most of those values will be "No", as the program
will perform an action only for a few records. This is all to avoid
hard-coded checks.

The real situation is way too business related to explain, but as an
example the following will do:
table:
Furniture
attributes:
ID
Name
HasLock
At the beginning, only a cupboard will have the attribute HasLock to
Yes, all the other records will have this attribute to No.
The program which uses these records will treat it in a way like:
.... IF Haslock=yes THEN CreateLock()
In this way, it is very easy for the user to create a "Commode" and
let the program create a lock for it, without having to change the
program.
But only very few records will have the HasLock attribute to Yes.
Furthermore, as many tests in the program depend on attributes of the
furniture (HasWheels, NeedToBePaint, ...), the table will have a lot of
attributes having Yes/No values. And every change request to my
program will probably end up with the creation of an additional column
in the table.

Now, I came up with an idea of setting up a table structure in two
levels:
the first level containing a unique id and the name, and a second
table with only those attributes which are "Yes":
Table:
Furniture
Attributes:
ID
Name

Table:
Furniture-attribute
Attributes:
Furniture-ID
Attribute-Name

This will generate records in the Furniture table like:
( 123, "Cupboard")
( 456, "Chair")
and records in the Furniture-attribute table like:
( 123, "HasLock")
( 456, "HasWheels")

To be more flexible, I would extend the Furniture-attribute table
like:
Table:
Furniture-attribute
Attributes:
Furniture-ID
Attribute-Name
Attribute-Type
Attribute-Value
Validity-begin-date
Validity-end-date
....so I can "switch on and off" attributes on any date in the future.

What are the pro's and con's for such an approach?

Anja.

 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Bob Badour

External


Since: Feb 14, 2006
Posts: 1573



(Msg. 2) Posted: Thu Aug 30, 2007 3:04 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

wrote:

> Hi,
>
> I have a to design a structure for a table with many attributes
> containing Yes of No values. The program I write, has to react in
> different ways corresponding the Yes/No-values in the different
> attributes.
> For many records most of those values will be "No", as the program
> will perform an action only for a few records. This is all to avoid
> hard-coded checks.
>
> The real situation is way too business related to explain, but as an
> example the following will do:
> table:
> Furniture
> attributes:
> ID
> Name
> HasLock
> At the beginning, only a cupboard will have the attribute HasLock to
> Yes, all the other records will have this attribute to No.
> The program which uses these records will treat it in a way like:
> ... IF Haslock=yes THEN CreateLock()
> In this way, it is very easy for the user to create a "Commode" and
> let the program create a lock for it, without having to change the
> program.
> But only very few records will have the HasLock attribute to Yes.
> Furthermore, as many tests in the program depend on attributes of the
> furniture (HasWheels, NeedToBePaint, ...), the table will have a lot of
> attributes having Yes/No values. And every change request to my
> program will probably end up with the creation of an additional column
> in the table.
>
> Now, I came up with an idea of setting up a table structure in two
> levels:
> the first level containing a unique id and the name, and a second
> table with only those attributes which are "Yes":
> Table:
> Furniture
> Attributes:
> ID
> Name
>
> Table:
> Furniture-attribute
> Attributes:
> Furniture-ID
> Attribute-Name
>
> This will generate records in the Furniture table like:
> ( 123, "Cupboard")
> ( 456, "Chair")
> and records in the Furniture-attribute table like:
> ( 123, "HasLock")
> ( 456, "HasWheels")
>
> To be more flexible, I would extend the Furniture-attribute table
> like:
> Table:
> Furniture-attribute
> Attributes:
> Furniture-ID
> Attribute-Name
> Attribute-Type
> Attribute-Value
> Validity-begin-date
> Validity-end-date
> ...so I can "switch on and off" attributes on any date in the future.
>
> What are the pro's and con's for such an approach?
>
> Anja.

Why not just have a "HasLock" relation with the id's of the furniture
with locks? And a "HasWheels" relation with the id's of the furniture
with wheels?

 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Neo

External


Since: Jul 24, 2007
Posts: 29



(Msg. 3) Posted: Thu Aug 30, 2007 4:02 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

> Table:
> Furniture-attribute
> Attributes:
> Furniture-ID
> Attribute-Name
> Attribute-Type
> Attribute-Value
> Validity-begin-date
> Validity-end-date
>
> What are the pro's and con's for [above] approach?

Pros: Flexiblity, less NULLs, ability to add new attributes on-the-fly
without having to modify db schema, less impact on code.
Cons: Less SQL support, more initial coding, slower performance.

Alternatively, in a database that employs a network of nodes, whether
things have samilar or different attributes makes little difference,
requires no schema and does not incurr NULLs. Following dbd script
models some furniture:

(; Note: is, from, to are part of system data)
(new 'component)
(new 'valid)

(new 'table1 'furniture)
(set+ table1 component 'wheel)

(new 'cabinet2 'furniture)
(set+ cabinet2 component 'wheel)
(set+ cabinet2 component 'shelve)
(set+ cabinet2 component 'lock)

(new 'desk3 'furniture)
(set+ desk3 component 'lock)
(set desk3 component (val+ 'drawer)
is valid
from (new '070101 'date)
to (new '080101 'date))


(; Get furnitures with wheel)
(; Gets table1 and cabinet2)
(get * component wheel)

(; Following expression get node that represents:
desk3 component drawer is valid from 070101 to 080101)
(new '060101 'date) (new '080101 'date)
(get (get furniture instance *) component (get component instance *)
is valid
from (>= (get date instance *) 060101)
to (<= (get date instance *) 080101))


For related example "Persons with Varied Attributes", see
www.dbfordummies.com/example/ex020.asp
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
anjasmedts

External


Since: Aug 30, 2007
Posts: 3



(Msg. 4) Posted: Fri Aug 31, 2007 6:15 am
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

> Why not just have a "HasLock" relation with the id's of the furniture
> with locks? And a "HasWheels" relation with the id's of the furniture
> with wheels?- Tekst uit oorspronkelijk bericht niet weergeven -

Your solution creates a lot of similar tables, whereas mine groups all
those tables into one.
Creation of new attributes on the fly is done adding a record in my
solution and creating a new table in yours.
So my opinion is that my solution is preferable.

But I'm open to all remarks!

Anja.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Bob Badour

External


Since: Feb 14, 2006
Posts: 1573



(Msg. 5) Posted: Fri Aug 31, 2007 1:32 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

wrote:

>>Why not just have a "HasLock" relation with the id's of the furniture
>>with locks? And a "HasWheels" relation with the id's of the furniture
>>with wheels?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> Your solution creates a lot of similar tables, whereas mine groups all
> those tables into one.
> Creation of new attributes on the fly is done adding a record in my
> solution and creating a new table in yours.
> So my opinion is that my solution is preferable.
>
> But I'm open to all remarks!
>
> Anja.

Why is it preferable? Are you allergic to tables?
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
David Cressey

External


Since: Mar 19, 2007
Posts: 550



(Msg. 6) Posted: Fri Aug 31, 2007 1:59 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

wrote in message

> > Why not just have a "HasLock" relation with the id's of the furniture
> > with locks? And a "HasWheels" relation with the id's of the furniture
> > with wheels?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> Your solution creates a lot of similar tables, whereas mine groups all
> those tables into one.
> Creation of new attributes on the fly is done adding a record in my
> solution and creating a new table in yours.
> So my opinion is that my solution is preferable.
>
> But I'm open to all remarks!
>
There are two schools of thought as to whether the addition of a new
attribute should or should not require additional data definition.

Adding a row to a table is data manipulation. altering a table to add a new
column is data definition. Creating a new table is likewise data
definition.

I'm of the school of thought that data in a database is managed better when
the right to create and alter data definitions is severely restricted,
while the right to manipulate data is extended to the applications that
interact with the database.

The other school of thought is that the superior flexibility of defining new
attributes without altering any database definitions overrules the data
management advantages in prohibiting the same. I can't defend this school
of thought (apparently yours) because, in my view, it leads inexorably to
undocumented and therefore unusable data.


Hope this helps.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
anjasmedts

External


Since: Aug 30, 2007
Posts: 3



(Msg. 7) Posted: Sat Sep 01, 2007 4:03 am
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

> There are two schools of thought as to whether the addition of a new
> attribute should or should not require additional data definition.
>
> Adding a row to a table is data manipulation. altering a table to add a new
> column is data definition. Creating a new table is likewise data
> definition.
>
> I'm of the school of thought that data in a database is managed better when
> the right to create and alter data definitions is severely restricted,
> while the right to manipulate data is extended to the applications that
> interact with the database.
>
> The other school of thought is that the superior flexibility of defining new
> attributes without altering any database definitions overrules the data
> management advantages in prohibiting the same. I can't defend this school
> of thought (apparently yours) because, in my view, it leads inexorably to
> undocumented and therefore unusable data.
>
> Hope this helps.

Good point, David.
Although here I've found a lot of small attributes only containing Y/N-
values, for which nobody knows what they're standing for. When
creating new records, they are always filled with the default. It's a
beautiful example that adding rows does not lead to more documented
use.

I do not belong to any school, I'm only open-minded.
I think I look to data the wrong way. IfI see records with many
attributes, mostly all 'N', except for the column HasWheels, I get
confused.
When I only see one record telling (123, HasWheels,Y), it points me
directly to the fact that furniture 123 has something special: it has
wheels!

Is adding a column not forcing the application to stop as it will lock
the entire table, or am I wrong?

Anja.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
David Cressey

External


Since: Mar 19, 2007
Posts: 550



(Msg. 8) Posted: Sat Sep 01, 2007 3:58 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

wrote in message

> > There are two schools of thought as to whether the addition of a new
> > attribute should or should not require additional data definition.
> >
> > Adding a row to a table is data manipulation. altering a table to add a
new
> > column is data definition. Creating a new table is likewise data
> > definition.
> >
> > I'm of the school of thought that data in a database is managed better
when
> > the right to create and alter data definitions is severely restricted,
> > while the right to manipulate data is extended to the applications that
> > interact with the database.
> >
> > The other school of thought is that the superior flexibility of defining
new
> > attributes without altering any database definitions overrules the data
> > management advantages in prohibiting the same. I can't defend this
school
> > of thought (apparently yours) because, in my view, it leads inexorably
to
> > undocumented and therefore unusable data.
> >
> > Hope this helps.
>
> Good point, David.
> Although here I've found a lot of small attributes only containing Y/N-
> values, for which nobody knows what they're standing for. When
> creating new records, they are always filled with the default. It's a
> beautiful example that adding rows does not lead to more documented
> use.
>
> I do not belong to any school, I'm only open-minded.
> I think I look to data the wrong way. IfI see records with many
> attributes, mostly all 'N', except for the column HasWheels, I get
> confused.
> When I only see one record telling (123, HasWheels,Y), it points me
> directly to the fact that furniture 123 has something special: it has
> wheels!
>
> Is adding a column not forcing the application to stop as it will lock
> the entire table, or am I wrong?
>
It depends on which DBMS you are using. The best case I have seen (some ten
years ago) was where the DBMS altered the table in the context of a
transaction. Running applications would be forced to block (not stop)
while the table was altered, and when the transaction committed, would be
allowed to resume.

I can't speak for any of the major DBMS products in the field today. They
may require you to stop or even recompile the application in order to get
the desired result. And adding a column raises a possible area of
ambiguity.

If the application contains the construct "select * from" it's possible
that the columns delivered may be different from the programmer's intent at
the time of writing the query. The programmer may have intended to retrieve
all the columns that existed at that time, not all the columns that are
added sometime in the future. Hence the application may become broken after
recompile.

This is a slippery slope.

Programmers generally prefer to go the way you went, and deal with
undocumented data. Programmers have never liked data mangagement under
somebody else's control. They didn't like it in the days of indexed files.
They didn't like it in the days of CODASYL databases. They didn't like it
in the early days of SQL databases. They still don't like it.

I used to be a programmer in a former lifetime. But I've been managing data
or databases for so long now that I might as well consider myself a
non-programmer. Undocumented data is practically unmanageable as far as I'm
concerned.

This is a very deep divide in the world of IT. I have only scratched the
surface in my repsonses.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Kevin Kirkpatrick

External


Since: Aug 28, 2007
Posts: 2



(Msg. 9) Posted: Mon Sep 03, 2007 6:24 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

On Sep 1, 2:03 am, wrote:
> > There are two schools of thought as to whether the addition of a new
> > attribute should or should not require additional data definition.
>
> > Adding a row to a table is data manipulation. altering a table to add a new
> > column is data definition. Creating a new table is likewise data
> > definition.
>
> > I'm of the school of thought that data in a database is managed better when
> > the right to create and alter data definitions is severely restricted,
> > while the right to manipulate data is extended to the applications that
> > interact with the database.
>
> > The other school of thought is that the superior flexibility of defining new
> > attributes without altering any database definitions overrules the data
> > management advantages in prohibiting the same. I can't defend this school
> > of thought (apparently yours) because, in my view, it leads inexorably to
> > undocumented and therefore unusable data.
>
> > Hope this helps.
>
> Good point, David.
> Although here I've found a lot of small attributes only containing Y/N-
> values, for which nobody knows what they're standing for. When
> creating new records, they are always filled with the default. It's a
> beautiful example that adding rows does not lead to more documented
> use.
>
> I do not belong to any school, I'm only open-minded.
> I think I look to data the wrong way. IfI see records with many
> attributes, mostly all 'N', except for the column HasWheels, I get
> confused.
> When I only see one record telling (123, HasWheels,Y), it points me
> directly to the fact that furniture 123 has something special: it has
> wheels!
>
> Is adding a column not forcing the application to stop as it will lock
> the entire table, or am I wrong?
>
> Anja.- Hide quoted text -
>
> - Show quoted text -

Another question - if a programmer writes code for furniture with
wheels, he might write code along the lines of:
SNIPPET1:
IF furniture_id in (select furniture_id from furniture_properties
where property = 'HasWheeels' AND value = 'Y')
THEN...
ELSE...
END IF;

SNIPPET2:
IF furniture_id in (select furniture_ID from HasWheeels)
THEN...
ELSE...
END IF;

Do you think she'd like to find out about her typo at compile time or
at runtime? With the typo corrected, which code do you consider more
readable? Which do you expect to have a greater potential for
performance issues?
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Brian Selzer

External


Since: Jun 16, 2005
Posts: 619



(Msg. 10) Posted: Tue Sep 04, 2007 4:51 am
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

wrote in message

>> Why not just have a "HasLock" relation with the id's of the furniture
>> with locks? And a "HasWheels" relation with the id's of the furniture
>> with wheels?- Tekst uit oorspronkelijk bericht niet weergeven -
>
> Your solution creates a lot of similar tables, whereas mine groups all
> those tables into one.
> Creation of new attributes on the fly is done adding a record in my
> solution and creating a new table in yours.
> So my opinion is that my solution is preferable.
>
> But I'm open to all remarks!
>

You are vastly oversimplifying. If you need to create a new attribute, you
need to add a record for each piece of furniture to which that attribute
applies. Your solution also makes it very difficult to define constraints.
How, for example, do you control which attributes apply to which types of
furniture? How do you prevent the insertion of a record that does not make
sense? Constraints are supposed to be defined and enforced in the database,
not the application. Stuffing everything into one table increases the
complexity of those constraints. You can't even use simple type checking,
since (correct me if I'm wrong) the attribute, Value, can have values with
different types! There is nothing wrong with having a lot of similar
tables. Having a separate relation for each type of atomic fact is a good
thing, because it simplifies the representation of information.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Authorised User

External


Since: Oct 25, 2007
Posts: 3



(Msg. 11) Posted: Thu Oct 25, 2007 2:57 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

On Tue, 04 Sep 2007 00:51:51 -0400, Brian Selzer wrote:

> <snip>
> ...There is nothing wrong with having a lot of similar
> tables. Having a separate relation for each type of atomic fact is a
> good thing, because it simplifies the representation of information.

I'm not convinced there's nothing wrong with having lots of tables.
Here's a specific example I'm thinking of: One failing project at my
place records "peer esteem" measures for academic staff. There is one
table for books published, one for papers published, one each for
editorships, keynote speeches, and consultancy work etc. The disadvantage
was because each table had a slightly different structure, they needed
specific programming to handle each table. The information they need to
record is for accreditation and, as the accrediting organisation is
external, the requirements for what they need to store are driven by the
external organisation, and very occasionally change. The (fixed size)
development team is delivering the application in stages, so there are
versions in Dev, QA and Prod. They found the burden of
development/maintenance slowly increased in proportion to the number of
tables (consequently development work slowed) until the development team
couldn't manage the load any more. Now here's the killer – I looked at
the information to be entered into the system (provided by the academics
themselves, doubtless scraped from a personal web page) and it was given
to me via an Excel spreadsheet: one column for what the academic did, and
a second for the date! So they now have the EXTRA work of trying to sort
that out, and then work out which table each line goes into. Such is
life, they gave an empty database to the academics and told them to fill
in the details.

In the successor project, I have a table called Activity_Type with
OVER EIGHTY rows, which replaces all of that in one go – one row for
each table they would've had if they ever got to the end of the job. In
my system, Person_Institution is many-to-many and each row has
an Activity_Type_Id, a free text description for most entries, and an
XML document for some client-defined items that MUST be specified for
some activity types only; I rejected the previous approach after and got as
close as I could to the other extreme.

So, having lots of tables is good from a data-purity point of view... but
spare a thought for the poor programmer who has to code for each table.
One general table is far from perfect, and we will lose of a great many
specific constraints that could help reduce data integrity problems... BUT
we think we can run with it. In this particular case.

--
Newlan's Truism:

An "acceptable" level of unemployment means that the
government economist to whom it is acceptable still has a job.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
-CELKO-

External


Since: Jun 29, 2007
Posts: 16



(Msg. 12) Posted: Fri Oct 26, 2007 9:19 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Every few months a Newbie re-invented the EAV fallacy and thinks they
are so clever. A really good horror story about this kind of disaster
is at:

http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Brian Selzer

External


Since: Jun 16, 2005
Posts: 619



(Msg. 13) Posted: Sat Oct 27, 2007 7:02 am
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"Authorised User" wrote in message

> On Tue, 04 Sep 2007 00:51:51 -0400, Brian Selzer wrote:
>
>> <snip>
>> ...There is nothing wrong with having a lot of similar
>> tables. Having a separate relation for each type of atomic fact is a
>> good thing, because it simplifies the representation of information.
>
> I'm not convinced there's nothing wrong with having lots of tables.
> Here's a specific example I'm thinking of: One failing project at my
> place records "peer esteem" measures for academic staff. There is one
> table for books published, one for papers published, one each for
> editorships, keynote speeches, and consultancy work etc. The disadvantage
> was because each table had a slightly different structure, they needed
> specific programming to handle each table. The information they need to
> record is for accreditation and, as the accrediting organisation is
> external, the requirements for what they need to store are driven by the
> external organisation, and very occasionally change. The (fixed size)
> development team is delivering the application in stages, so there are
> versions in Dev, QA and Prod. They found the burden of
> development/maintenance slowly increased in proportion to the number of
> tables (consequently development work slowed) until the development team
> couldn't manage the load any more. Now here's the killer - I looked at
> the information to be entered into the system (provided by the academics
> themselves, doubtless scraped from a personal web page) and it was given
> to me via an Excel spreadsheet: one column for what the academic did, and
> a second for the date! So they now have the EXTRA work of trying to sort
> that out, and then work out which table each line goes into. Such is
> life, they gave an empty database to the academics and told them to fill
> in the details.
>
> In the successor project, I have a table called Activity_Type with
> OVER EIGHTY rows, which replaces all of that in one go - one row for
> each table they would've had if they ever got to the end of the job. In
> my system, Person_Institution is many-to-many and each row has
> an Activity_Type_Id, a free text description for most entries, and an
> XML document for some client-defined items that MUST be specified for
> some activity types only; I rejected the previous approach after and got
> as
> close as I could to the other extreme.
>
> So, having lots of tables is good from a data-purity point of view... but
> spare a thought for the poor programmer who has to code for each table.
> One general table is far from perfect, and we will lose of a great many
> specific constraints that could help reduce data integrity problems... BUT
> we think we can run with it. In this particular case.
>

It is a truly poor programmer indeed who would trade off data integrity for
a faulty expectation of reduced work. If there are eighty types of atomic
facts, then whether you're coding for one table or for eighty, you still
have to code for eighty types of atomic facts. Is it really less work to
lump it all together?

> --
> Newlan's Truism:
>
> An "acceptable" level of unemployment means that the
> government economist to whom it is acceptable still has a job.
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
Roy Hann3

External


Since: Oct 20, 2004
Posts: 472



(Msg. 14) Posted: Sat Oct 27, 2007 3:57 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"Brian Selzer" wrote in message

>
> "Authorised User" wrote in message
>

[snip]

>> So, having lots of tables is good from a data-purity point of view... but
>> spare a thought for the poor programmer who has to code for each table.
>> One general table is far from perfect, and we will lose of a great many
>> specific constraints that could help reduce data integrity problems...
>> BUT
>> we think we can run with it. In this particular case.
>
> It is a truly poor programmer indeed who would trade off data integrity
> for a faulty expectation of reduced work. If there are eighty types of
> atomic facts, then whether you're coding for one table or for eighty, you
> still have to code for eighty types of atomic facts. Is it really less
> work to lump it all together?

Unfortunately, given the piss-poor tools that programmers are content to
use--for a whole lot of reasons that I could rage about at length some other
time--the answer to your question isn't as clear as it should be. I will
give you one example from my own recent experience that illustrates the
point.

The client, the taxation authority of a small European country, had a
requirement to provide an application to its helpdesk users who handle calls
from the public about their personal tax returns. The application needed to
present the helpdesk users with a myriad details about each of the tax
allowances and credits each taxpayer might claim. At the time there were
about 120 different allowances for everything from mortgage interest relief
on investment properties to vet bills for guide dogs for the blind, and
everything in between. The descriptions of the benefits were wildly
different (as you might guess), and some required a variable number of
diverse supplimentary facts. (In terms of complexity this subsystem dwarfed
the rest of the tax system by an order of magnitude.)

To make this truly wild, the system also had to be able to respond to annual
changes in tax legislation, within a legally defined six week period. To
fail to implent an announced change within six weeks would require going
back to the legislature and getting them to literallly change the law of the
land. You can see where this is going.

Now normally I only get involved with performance issues at this site, but
having demonstrated how the database design can strongly influence the
performance of the applications, they invited me to review the proposed
solution, which was absolutely classic EAV. To cut a long story short I
told them, in terms, that they were embarking on a "massive blunder", and
had an absolutely bruising meeting with several customer representative and
also the prime contractor. It was 10 against 1, and my poor choice of words
ensured I had no allies in the room at all. But what sealed my fate was the
fact that my recommended solution (i.e. as many tables as it takes) just
wouldn't work in their environment. The DBMS could handle the requirement
in its sleep, and it probably would not have been any more difficult to code
a sufficiently versatile front-end application (though more about that
below). But what killed it dead was the middleware, which was some
extremely over-mature version of Tuxedo that couldn't be replaced for a
whole lot of very good (expensive) reasons. It turned out that this rev of
Tuxedo could support only 128 distinct services, of which something like 100
were already in use. (I may not have the details right, but the point is
Tuxedo imposed severe constraints.) Within their existing architecture my
solution would have required n-hundred additional services, where n would
grow each year.

My proposed solution to that problem was a single dynamic service. The
database would have its hundreds of tables, and there would be a single
service that would use dynamic SQL to query/update them. It would convert
the data to EAV for transit, but it would be done automatically using the
fully-documented, standard meta-data from the DBMS, and the process could
be revered at the client end to recover the original tables. Elegant, I
thought.

But no. This was unfamiliar territory for the programmers, who necessarily
always outnumber the database wonks (precisely because they are so
unproductive and therefore you need lots of them). There was just no
persuading them that dynamic SQL is respectable, and enshrined in the
ANSI/ISO standards. To them it was not a basic skill that they ought to
have mastered in school, nor even a skill to be acquired within a one-day
on-the-job training course. It was impossibly esoteric, risky, obscure, and
just not going to happen. So not only would they not build a generic,
dynamic Tuxedo service driven by the DBMS's own meta-data that they get for
free, they would not build a client application that used the same meta-data
to recover the tables from the EAV stream. To them, it was less risky to
re-invent that meta-data their own way and store the all data in a single
bloated EAV table in the database and build a single generic service that
way.

In case anyone is wondering how it turned out, well, today it stumbles
along. It has so-far appeared to meet the need for rapid change, at least
in the sense that no one can tell if it doesn't work. What is not so clear
is whether anyone has checked how reliable it is, and what is certain is
that even simple queries are insanely complex; there are no integrity
constraints of any kind, and no way to verify that every application is
treating the meta-data consistently. Unfortunately, although this is the
most egregious waste of expensive DBMS technology in the system, it is also
fairly typical. Expections are very low both for run-time performance and
for programmer productivity. This subsystem did not stand out as being
particularly bad by the local standards.

So, if programmers are content to re-invent the wheel, and if they regard
dynamic SQL as being too esoteric (or worse, they simply don't know about it
and don't know the DBMS already provides more and better meta-data than they
can devise--plus the logic to support it), then to them it could well look
like EAV is less work. They are imagining they have to code 200 distinct
services by hand to support 200 distinct tables. To them, the math shows
EAV is cheaper.

(Note that I've written about dynamic SQL here. There are other tools
available and one could imagine even better ones bing invented. I am not
suggesting dynamic SQL is the only solution. I am just pointing out that at
least one pretty good solution exists and one possible explanation why no
one uses it.)

Roy
 >> Stay informed about: Attribute-values in separate table 
Back to top
Login to vote
paul c

External


Since: Oct 05, 2007
Posts: 189



(Msg. 15) Posted: Sat Oct 27, 2007 5:57 pm
Post subject: Re: Attribute-values in separate table [Login to view extended thread Info.]
Imported from groups: per prev. post (more info?)

Back to top
Login to vote
Display posts from previous:   
Related Topics:
Logical Attribute or not - I am modeling a table based on the following form example. Form Section: Mood =============== Use the following codes to indicate patterns of mood disorder. 0. Never present 1. Present in last 7 days and was present before 2. Present in last 7 days an...

Relation or attribute and why - Let's say that we have a noun present in a conceptual data model, but not identified as a strong entitiy (recognizing not all conceptual modeling techniques use such distinctions). What are the conditions under which this noun will/should translate into...

attribute name conflicts - I believe Codd originally envisaged "tables" that had what he called domain names to identify "columns" but that in his second paper, he introduced what we know of as attribute names because he wanted, for example, to allow for rel...

Multiple-Attribute Keys and 1NF - I am still fighting with the theoretical underpinning for 1NF. As such, any comments would be greatfully accepted. The reason for my concern is that there /seems/ instances where 1NF is insufficient. An example occurred to me while I was wiring up a..

Attribute name prefixes, domains, joins, ISO 11179 - Some thoughts on attribute names. A common response to inexperienced database developers who post DDL or pseudo-DDL is that they have mixed up the physical model with the logical model, or the logical and conceptual model, because they have prefixed thei...
   Database Forums (Home) -> Technology and Theory All times are: Pacific Time (US & Canada)
Goto page 1, 2
Page 1 of 2

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]