Re: trees, trees, trees

From: Peter Schneider <pschneider1968_at_googlemail.com>
Date: Mon, 25 Jun 2012 00:13:32 +0200
Message-ID: <js83gk$nks$1_at_online.de>

Am 22.06.2012 02:03, schrieb geos:
> Peter Schneider wrote:
>> Are you then, in turn, going to share part of your pay check with Gerard? ;-)
>
> do you think this is the only criteria people help other people? but yes, I
> would consider that if I knew more details.
>
>> That is not a database table. It has no primary key, no constraints, no
>> indexes... It is just a dumped Excel table. Any query against it will always
>> do full table scans.
>
> it doesn't have to have, because there is no constraints nor primary key. but

I strongly disagree. Each table in a relational database has to have at least a primary key (and it should better be one with inherent meaning, not just a sequence). Otherwise it is not a relational table, but just a data dump carrying no meaning whatsoever.

Please consider: if you don't have a PK, then not even the uniqueness of a complete record is guarenteed, records can just be duplicated as often as coincedence, bad luck or program bugs do like to make it happen. And now think further what happens if you join such a "table" to other table in complex queries, e.g. in aggregation queries computing sums and such. Get the point?

You should better rethink you design, or you will run into big data consistency problems later. Trust me, I've seen such shit happen with bad design.

And if you have a tree model, you should be able to answer these question:

• can an entity instance which occurs as a child record in the tree be uniquely identified only because of its position in the tree hierarchy or is in some way uniquey by itself?
• is it allowed to have multiple instances of the same child element to appear at the same level (as children of the same parent) or is this disallowed?
• is it allowed to have multiple roots in the tree, or is only one root allowed?

All these findings need to be enforced by constraints, otherwise your tree data is going to violate the rules at some point in time or the other.

If you haven't thought yet about such kind of questions, my guess is that you probably haven't thought deep enough about the entities in real life that your DB is trying to model.

Regards
Peter

-- 
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you.                    -- David McCullough Jr.

Received on Sun Jun 24 2012 - 17:13:32 CDT