Constraints and Foreign Keys....

Hi,
I am currently in the design phase. I have a question about the possibility
of creating a certain type of constraint. So far, I don't think its
possible.
For the sake of the example say I have two entities, Trees and Cars. I keep
track of entities in another entity called Entity Types. This would contain
car and tree as a record. Then I have another list of entities called
characteristics. Certain charateristics will only apply to the car, others
to the tree and some to both. Example characteristics would be model,
manufacturer, species, and name.
So I created an Entity Assignable Charateristics. This will hold which
charateristics can be applied to which entity. Some of those will be
required, others won't. I could add an attribute "Required" to keep track o
f
this.
Charateristics for instances cars or trees will be stored in either Entity
Charateristics or in Car Charateristics and Tree Charateristics.
Here's my question. If in Entity Assignable Charateristics there are
indicated certain required charateristics for trees, is there a way I can
enforce that in Entity Charateristics or Tree Charateristics.
Thanks in advance.
Pete>> I have two entities, Trees and Cars. I keep track of entities in another
entity called Entity Types. <<
This is an OO approach to the world and NOT an RDBMS. Separate types
of entieis have their own tables; there is no such monster as a
"magical, vague, general thingie" set. To be is to be something in
particular, not everythign in general.|||Start by googling for "EAV design flaws".
Inability to impose appropriate integrity constraints is the primary
drawback of approaches similar to the one you propose.
Anith|||Hi,
Thanks for the response. I just want to make sure I understand you
correctly. You think that I should have Trees and Cars. Then you think I
should have Tree Assignable Charaterisics and Car Assignable Charateristics.
These hold all the possible charateristics for a tree or car respectively.
This makes sense.
Now if I create a Tree Charateristics entity, which holds all the
charateristics for each instance of a tree, how can I make sure that the
charateristics that are placed into this entity come from the Tree Assignabl
e
Charateristics subset or Charateristics?
Additionally, how would required charateristics play into this?
Thanks again.
Pete
"--CELKO--" wrote:

> This is an OO approach to the world and NOT an RDBMS. Separate types
> of entieis have their own tables; there is no such monster as a
> "magical, vague, general thingie" set. To be is to be something in
> particular, not everythign in general.
>|||Hi,
Thanks for the comment. The reason I have the entities I do is so people
can easily create a new tree or car or charateristic. However, one person
may wish to make the charaterisic model for a car required, another may not.
Since you seem to think I have a flaw in my design, could you suggest anothe
r
way I might accomplish what it is I am trying to do? Thanks again.
Peter
"Anith Sen" wrote:

> Start by googling for "EAV design flaws".
> Inability to impose appropriate integrity constraints is the primary
> drawback of approaches similar to the one you propose.
> --
> Anith
>
>|||>> The reason I have the entities I do is so people can easily create a new
Without a comprehensive knowledge of your business model, others cannot
possibly determine the characteristics of an entity of your enterprise.
Rather than thinking about trees, cars and characteristics, think of trees
and cars as a set of entities having their own attributes. In loose terms,
each form an entity type & therefore belong to separate tables.
Think of each table as a set of propositions, or statement of facts.
In a nutshell, with a relational database, rows represent facts about real
world entities, column values represent their properties and relational
operations answer the questions about them.
Anith|||Thanks for the comments. Let me start by saying I agree with your statement
.
However, it is a business requirement that a customer can create custom
attributes. That is in essence what I need to figure out.
I certainly do not want to give the user the ability to add a column to a
table, so it seems as though I need to use this EAV design. I am troubled b
y
it since I seem to be losing the ability to put certain restraints on the
data. Do you know of another way to handle custom attributes?
Thanks again.
"Anith Sen" wrote:

> Without a comprehensive knowledge of your business model, others cannot
> possibly determine the characteristics of an entity of your enterprise.
> Rather than thinking about trees, cars and characteristics, think of trees
> and cars as a set of entities having their own attributes. In loose terms,
> each form an entity type & therefore belong to separate tables.
>
> Think of each table as a set of propositions, or statement of facts.
> In a nutshell, with a relational database, rows represent facts about real
> world entities, column values represent their properties and relational
> operations answer the questions about them.
> --
> Anith
>
>|||Pete,
On what basis does a customer create those attributes? How does one make
sure the attributes are appropriate to the entity that is being represented
in the table? How does one constrain the values to be drawn from a domain
specific to that attribute?
It sure sounds like a serious misunderstanding of business requirements.
There is no such thing as a custom attribute. Either an entity has an
attribute or it does not. It is up to the database designer to determine all
the applicable attributes of relevance pertaining to an entity in the
conceptual model, before starting logical design. In general, when one
chooses to ignore this exercise, often he is forced to compensate by forcing
the user to share the burden of creating tables, columns, constraints etc.
Generally, the database design flows from conceptual -> logical -> physical
levels.
-- Understand the business model thoroughly so that you will know the
entities, relationships among them and the attributes involved.
-- From this business model ( also known as conceptual schema ) applying
relational principles, map them relational tables. Use proven design
guidelines like normalization, predicate uniqueness principle etc. to
eliminate data redundancy. A good relational design allows the data to be
represented without application or task bias.
-- Based on the capabilities of the SQL DBMS, tune up the physical model so
the data can be efficiently manipulated and retrieved.
Many a time, designers introduce complexity at the logical level in an
attempt to compensate for an incomplete business model. Similarly complexity
is often introduced at the physical level when trying to compensate for a
mediocre logical model as well.
Anith|||Yes, it is the same. Initially, they called it New Design Priciple and later
renamed to the Principle of Orthogonal design.
Anith|||Thanks for the response. These are all questions I am grappling with. The
example that I started with illustrates this point well. I need to create
new trees, new cars and new charateristics that could be applied to either.
We are an ISV so customers are supposed to be able to create their own
characteristics (i.e. attributes). If you know of another way to handle thi
s
(without changing the table structure), please let me know. Any thoughts
would be great.
Thanks again.
"Anith Sen" wrote:

> Pete,
>
> On what basis does a customer create those attributes? How does one make
> sure the attributes are appropriate to the entity that is being represente
d
> in the table? How does one constrain the values to be drawn from a domain
> specific to that attribute?
> It sure sounds like a serious misunderstanding of business requirements.
>
> There is no such thing as a custom attribute. Either an entity has an
> attribute or it does not. It is up to the database designer to determine a
ll
> the applicable attributes of relevance pertaining to an entity in the
> conceptual model, before starting logical design. In general, when one
> chooses to ignore this exercise, often he is forced to compensate by forci
ng
> the user to share the burden of creating tables, columns, constraints etc.
> Generally, the database design flows from conceptual -> logical -> physica
l
> levels.
> -- Understand the business model thoroughly so that you will know the
> entities, relationships among them and the attributes involved.
> -- From this business model ( also known as conceptual schema ) applying
> relational principles, map them relational tables. Use proven design
> guidelines like normalization, predicate uniqueness principle etc. to
> eliminate data redundancy. A good relational design allows the data to be
> represented without application or task bias.
> -- Based on the capabilities of the SQL DBMS, tune up the physical model s
o
> the data can be efficiently manipulated and retrieved.
> Many a time, designers introduce complexity at the logical level in an
> attempt to compensate for an incomplete business model. Similarly complexi
ty
> is often introduced at the physical level when trying to compensate for a
> mediocre logical model as well.
> --
> Anith
>
>