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.

anjasmedts (AT) hotmail (DOT) com 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?

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

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.

anjasmedts (AT) hotmail (DOT) com wrote:

Why is it preferable? Are you allergic to tables?

<anjasmedts (AT) hotmail (DOT) com> wrote

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.

<anjasmedts (AT) hotmail (DOT) com> wrote

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.

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

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.

"Authorised User" <bg (AT) microsoft (DOT) com> wrote

I am not going to rehearse the familiar criticisms of the EAV model all over
again. I suspect you've already heard them and done the math and like your
answer. I don't, but I don't have to live with it.

What I will say is that once again we see an example of how the database is
completely flexible and even crappy present-day SQL implementations can
model most of the real world with almost trivial ease, but the application
programming is still trapped in about 1965 so we can't exploit it.

So,can we enquire into your example a little more to try to understand what
the problem is? It seems to me unlikely that the problem with the many
different table definitions is the different business logic associated with
the different fact types, because that doesn't go away when you use EAV (in
fact it only gets harder to handle). At first glance it looks like the
programmers just didn't want to use dynamic SQL, or whatever the equivalent
is in their language of choice. Or is it something more fundamental and
genuinely intractable?

Roy