In my data modeler software I see that I can set a relationship to
have a "mandatory child" which makes the realtionship 1 to 1 instead
of 0 to 1, in my particular case. However, I don't see the difference
in the script that it generates.

How does this relationship get enforced normally?
I need to know in order to make sure my inserts are proper.

As far as selecting, I seems that I would do an outter join if the
relationship was 0 to 1, and a normal join if it is 1 to 1.

But, if it isn't enforced somehow, than my normal join could
potentially fail.

On 02/17/2010 04:42 PM, cpisz wrote:
Two remarks: first, I am not sure this can be answered in a database
agnostic way. If I understand you correctly you have created a circular
dependency between tables because you need an FK from A->B and from
B->A. In Oracle for example you would need to defer constraint checking
until commit time because you typically insert records sequentially.

Which brings me to the second point: why don't you put all the data in a
single table if you need a fixed 1:1 relationship anyway? Retrieval of
corresponding data will be much more efficient than via joins. If you
need separate views on the data, you can create them.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Feb 17, 11:56*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Because marketing changes their minds on a daily basis on what data
they want or don't want to track anymore, or what they want to call
something, or they add and subtract enumerated types.
So, I figured it would be alot easier to seperate those entities out
into their own tables.

That way it is as easy for me as typing "drop table blah" or
If they have a new enumerated type then "insert into blah" or

If they were combined into one table, then reprogramming my middle
teir every time is more difficult.

"cpisz" <christopherpisz (AT) gmail (DOT) com> wrote

In most SQL DBMSs this is impossible to enforce declaratively. It has to be
implemented with procedural code and/or a "deferrable constraint" which is a
feature that temporarily allows the constraint to be broken. Relationships
that are mandatory on both sides are a common business requirement so this
is in fact a serious drawback of the SQL model.

--
David Portas

On 17.02.2010 23:17, cpisz wrote:
If this changes so often you may be better off with a more generic
solution where you store arbitrary attributes in a second table. Then
you do not need to do any schema level changes when they change their
mind. If OTOH this is going to be a product then I'd probably stick
with a regular schema, even if it means to change it several times
during development. My 0.02EUR.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/