AMO wrote:
Based on your desire for "practical" answers, AMO, I wonder what your
background is. For reference, here's a brief summary of mine:
BS Physics, I moved from research to programming manufacturing systems
and embedded systems. Along the way I got a MS Engineering. As the
needs of some embedded systems became greater, I moved into databases.
I've been involved in database work as my primary focus for over
10years now.

Now the reason I mention that. My scientific training emphasized that
when you build a model of something (in a database or in theoretical
equations), you must make choices about what you include and what you
leave out. And you cannot always know what a new factor might be
required/discovered. IOW no matter how hard you try, AMO, you cannot
predict the long term future. So sometime you have to adjust your model
(new tables in the database, new factors in the equations).

But it is not so bad. Within the area you want to model, you can have a
lot of flexibility. (That's one of the reasons in another thread I
mentioned Newton and Einstein. Newton's model is not wrong as long as
you only apply it in the proper context.) That screen layout database
is not so hard. You can get a lot of features implemented that do not
limit the users. Are you suggesting that your database design today
will be able to handle when browsers have to deal with 3D holographic
displays?

As I have said, you seem to choose these ID attributes as a first
choice. You seem to be the radical one here.

You ARE in control in the sense that you control what goes into your
model and ultimately into your database.

[other snip]

Well what you seem to be saying here is you want your DB to be
infinitely extensible. The datamodel for that is called the Relational
Model. Every Relational DBMS product have a version of the model, and
within the dbms it is called the data dictionary. Note that even the
vendors have had to add tables to cover new items that "needs to be
registered". Rather than create generic object tables the vendors see
the advantage of tables specific to the purpose. And the DBMS vendors
are fairly practical people, don't you think?

Ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

Mine's computer science all my life. As the years go by I have moved from
internal solutions to off the shelf solutions (i.e the move to software
houses) as maintaining internal development teams has become less viable. I
also have moved into fast changing environments - business processes change
and are more varied alongside integration requirements to external systems.
I have worked in databases for about 10 maybe 11 years now.

That is correct. However, when architecting your model, the emphasis is on
change management. The model you build especially when external system
integration is required or multiple database tiers are required require an
analysis on 'what if x changes'. Conceptual thinking does not take into
consideration this need.

No, let's not take things to extreme here. The systems built here in this
thread's example by the original poster or in my posts in the past simply
may require a technique where the conceptually correct may need to be
compromised against the practical implementation or use of the system.
Conceptual relational modelling is fine when you are in control of the
information and know what it is. When you don't know, if it requires
database changes and coding or referential data entry to adjust every time
there is a change, this is hardly a practical way forward. My example in a
previous post where an engineer goes out on site and then has to buy
additional items adhoc is a fine example. Codd followers would insist that
the engineer possibly phone up head office, get them to add the item to
ensure referential integrity to the item and then use the item. This is not
practical. Neither is getting everyone who would buy a paper from a
newsagents to register their details so that a customer id can form part of
a candidate key.

You talk about proper context, but the issue with dynamic attributes is not
an issue of the future - I am posing the question now if you were to design
it from the ground up in the relational model knowing that attributes of an
object will need to be stored, but you don't know what all of them are.

You, like Celko are simply thinking inside the box and not budging from
Codd's bible. The world is a more fast changing environment. Database
design and thinking will change with or without Codd and for one that works
in the real world of database systems, one must be able to adapt and to
adapt fast. What I do is not radical, its done all the time - the
difference is that I acknowledge why its done and the reasoning behind the
architect to balance the tradeoffs and requirements as opposed to some who
simply assume that its poor design and leave it at that. There are a lot of
systems out there with poor design - there is no two ways about that, but
you cannot assume that they all fall into that category simply because they
are not following Codd's rules. There are a lot of systems out there that
very well designed and do not completely follow Codds rules, but they are a
lot more practical and adaptable to change.

So.....if you are in control, how would you implement dynamic attributes?
The answer to this question is what separates the boys from the men and also
the flexible from the inflexible.


No, you're taking an extreme view. In all my posts, there have been areas
where I would break out of traditional relational modelling to overcome a
problem where the diehard conceptual model gives a lack of flexibility.
These are primarily (so far):
1) I would use an internal id as a primary key if records need to traverse
between an n-tier system. Other developers would implement the internal id
as a surragate key which in many cases is equally fine. However, by
reversing this, I can more easily afford for the primary key to be flexible
to changing requirements. In many database system, once the primary key has
been designed because of relational links, it is difficult to change and
cascade and do so in n-tiers.
2) I would design tables to capture adhoc circumstances than require
referential data entry which is not practical in the real world. An example
is an engineer and his van stock and if he ran out of items, going to the
local shop and purchasing similar subsititute items. The cost to have him
ring up head office, register the new item (which may not be desirable) have
the item transferred in some way to his handheld database, register stock
against the system in order to use the item is undesirable. Equally
undesirable is trying to botch your way around by having everything entered
under a miscellaneous code and try to kid yourself into thinking that simply
because the misc code is an actual database referenceable record, everything
is fine and dandy.
3) This current thread of how the original poster explained the layout of
the data which turned into the possible requirement to store dynamic
attributes. If this was the case, its a scenario where you do not know what
data is coming your way, and therefore you need to be able to analyse what
you do know, what you are likely to get in terms of data and also to cope
with what you don't know and architect accordingly. I am concerned as this
is where I haven't seen evidence of you being able to adapt to try and build
a system and weigh out the pros and cons of each way forward.

Consider the difference between TCP and UDP. The former contains a protocol
for handshaking to ensure that data is integral when transferred from A to
B. This is important for file transfer in general. Consider also the
concept of UDP transmission where data is just sent out and then forgotten
about. If the data does not arrive whole and complete, who cares. This
technique is used for broadcasting - if you miss a few items of information
in a live broadcast, there is no point in going back to get it as it is
useless when you do get it back as it is out of date.

Relational modelling is all very well and good, but there times when if you
need to create a seperate table or perform a database change just to be able
to receive the data, your solution may well be out of date. The original
poster has to be commended in his attempt to try and store attributes and
values in separate columns, but this has its flaws as I pointed out in my
first response to this thread..

My point is however, that neither you nor Celko appear to be able to come up
with a solution to the problem other than 'more research' when in reality
more research is not the problem here. It may help, but it wouldn't provide
a solution to ultimately what is a dynamic attribute requirement.

AMO

You make some interesting points.

Bad example. The ISBN-13 will put books under the EAN system for
identifying retail items, then in a few more years the GTIN (Global
Trade Id Number) will globalize everything! <evil laugh> Enemies of
Wal_mar, you are surely doomed !!! <evil laugh>

As an aside, look at the ISO tire size codes -- a great example of a
vector encoding scheme I have used in my books. Verification is
physical inspection; the old US system sucks and has no meaning.

But I would not add more and more columns. There would be a table of
tires and a table of books, which reference the inventory table via the
EAN or GTIN. The inventory table has their location in warehouses,
quantity on hand, price per unit, etc. The particular tables have the
details of each kind of entity.