Q: Is it me or is ADO.NET **BACKWARDS**?

[Somebody]

To simplify the question, I create 2 tables in SQL.

Table_1 has columns: ColumnA (PK), ColumnB and Type.
Table_2 has columns: A (PK) and Description

Now, I want to set things up so that Table_1.Type is of type Table_2.A.

1) I guess in SQL lingo, what I'm trying to say is that Table_1.Type is a FK
to Table_2.A. Is that correct terminology? Is that backwards? It appears to
me to be correct.

2) So I create the relationship in SQL and SQL names it FK_Table_1_Table_2.
In the Database diagram, the key icon is on the Table_2 side and I get the
little infinity sign on the Table_1 side. Is that correct or should it be
the other way around? From what I gather from looking online, the key icon
should be on the table with the PK and the infinity sign should be on the
other table, right?

3) OK, assuming that #1 & #2 are correct ... I then add the datasource to
my project and import the TABLES. Ado.net generates all the wrapper classes,
schemas, etc.

Now my confusion occurs...

FK_Table_1_Table_2 is defined as:

this.relationFK_Table_1_Table_2 = new
global::System.Data.DataRelation("FK_Table_1_Table_2",

new global::System.Data.DataColumn[]
{
this.tableTable_2.AColumn
},

new global::System.Data.DataColumn[]
{
this.tableTable_1.TypeColumn
},

false);


So, as you can see, the wizard added Table_2.A first, and then Table_1.Type.

According to MSDN, that makes Table_2.A the PARENT COLUMN and Table_1.Type
the CHILD COLUMN.

Isn't that backwards? Cuz if I have a Table_1 row and want to get its type
description (Table_2) I need to do:

DataRow[] r = row1.GetParentRows(ds.Relations["FK_Table_1_Table_2"]);

Or do I have the parent/child terms backwards in my head?

 

[Willem van Rumpt]

So, as you can see, the wizard added Table_2.A first, and then Table_1.Type.

According to MSDN, that makes Table_2.A the PARENT COLUMN and Table_1.Type
the CHILD COLUMN.

Isn't that backwards? Cuz if I have a Table_1 row and want to get its type
description (Table_2) I need to do:

DataRow[] r = row1.GetParentRows(ds.Relations["FK_Table_1_Table_2"]);

Or do I have the parent/child terms backwards in my head?

The latter, you have the definition backwards. From a foreign key point
of view, Table_2.A is the parent, Table_1.Type the child.

Visualize it using the following thought experiment:

Create a row in table_2
Create a row in table_1 referencing Table_2.A in Table_1.type
Try to delete the row in table_2

You can't (assuming the FK relation is setup properly):
A child can not exist without a parent.

 

[Andy O'Neill]

news:kYhen.77756$s%[email protected]...

1) I guess in SQL lingo, what I'm trying to say is that Table_1.Type is a
FK to Table_2.A. Is that correct terminology? Is that backwards? It
appears to me to be correct.

Well it ain't.

A foreign key is just a column has something in it denoting which entry each
row is related to.
It's a key to something.
Foreign just means a different table.

With 1:1 it doesn't matter which way round you do it.
With 1:M it does.

Say you have invoices and they can have multiple invoice lines.
You can't put 22 invoice line ids in the one field in an invoice row.
So you put invoice id into a column in invoice lines.
Each invoice line only relates to one invoice.

 

[vanderghast]

A 1:1 relation is NOTHING more than a 1:M relation where the Many side is
restricted to be AT MOST one. The cardinality of the rows matching relation
can be 0, or be 1. So, the way we read it MATTERS.

Example, a company offers an option (OptionPlan, and WeeklyContribution) to
each employee, two possible designs,

one with a 1:1 table Employee:EmployeeTakingTheOption, that last table
having the fields EmployeeID, OptionPan, and WeeklyContribution;

and a second design, add NULLable fields OptionPlan and
OptionWeeklyContribution to table Employee, with a table rule stating that
if OptionWeeklyContribution is null, then OptionPlan should also be null.

Clearly, the first design is easier to maintain if more than a single extra
field is required.

But in a 1:"1" relation, the way we look at it does matter.


Vanderghast, Access MVP

(...)

With 1:1 it doesn't matter which way round you do it.

(...)

 

[Andy O'Neill]

A 1:1 relation is NOTHING more than a 1:M relation where the Many side is
restricted to be AT MOST one. The cardinality of the rows matching relation
can be 0, or be 1. So, the way we read it MATTERS.

I don't know about your notation.
In mine 1:1 means there is exactly one record in the first table that
corresponds to one record in the related table.
No more, no less.

 

[vanderghast]

In a database a 1:1 relation does NOT requires there is a record on the
right side table.


=====================
Employee 'table name
EmployeeID, ... ' fields names
1010, ...
1011, ... ' some data, may have other rows
====================
with EmployeeID being the primary key, and


=====================
OptionEmployee 'table name
OptionID, EmployeeID ' fields names
1, 1011 ' the only data,a single row
======================


with an index with a unique constraint on OptionEmployee.EmployeeID, while
OptionID is the pk.

You CAN then have a 1:1 relation between Employee and OptionEmployee, and
it is a valid one EVEN if there is no EmployeeID=1010 in the last table. In
fact, simply define a 1:M relation and the database will immediately change
it as a 1:1. At least, that is how you do it and with Jet and with MS SQL
Server! You can't explicitly define a 1:1 relation. You define a 1:M and the
Many side, being constrained, you end up with a 1:1.

BUT you can't define the same relation starting with OptionEmployee toward
Employee (and enforce the relation, since 1010 won't be accepted! )


So a 1:1 is, in reality, a 1:M, where the Many side is constraint to AT
MOST one. The two ones, in 1:1, are FAR FROM being equivalent, since the
right one means either 0, either 1. And the case of 0 is as here up with
EmployeeID = 1010, totally absent in OptionEmployee.EmployeeID, even with a
1:1 relation perfectly holding and enforced, and that, as well with Jet as
with MS SQL Server.



Vanderghast, Access MVP

 

[Andy O'Neill]

In a database a 1:1 relation does NOT requires there is a record on the
right side table.

If you lived in a world where there is only access and sql server and you
ignore entity relationship modelling theory completely then you're right.
The notation comes from entity relationship modelling though.

1:1 means there is a matching record for all.
If one side is optional then the notation is 1:0..1
Where the many side is optional you can write 1:0..m
Sometimes slashes instead of the dots and there are a stack of different
ways of showing it on ER diagrams.

You assume optionality of relationships is implemented in all dbms in one
way.
It ain't.

 

[vanderghast]

Then, in general, the way we look at a 1:1 relation MATTERS, rather than the
reverse, as you claim.

Vanderghast, Access MVP

 

[vanderghast]

Furthermore, HOW you add data? I mean, how can you enter data in TWO tables,
at ONCE? if the relation is enforced, on your terms, it won't be possible to
enter data in one table and then, some nanosecond later, in the second table
(unless we disable the relation) since the system has no way to know there
is something to come, in few nanosec, the DRI will fait and the engine would
reject your attempt to insert data in the first table. Which database
engine proceed as you claim it does? Oracle? MySql? Ingress? ...


Vanderghast, Access MVP

 

[Andy O'Neill]

Furthermore, HOW you add data? I mean, how can you enter data in TWO
tables, at ONCE?

You never heard of Transactions?

if the relation is enforced, on your terms, it won't be possible to enter
data in one table and then, some nanosecond later, in the second table

You never heard of relationships enforced via triggers in sql server?

If you read anything about normalisation they usually say you normalise 1:1
relationshiops into one table.
However.
It's a common pattern to have two tables related 1:1 in packages that allow
the user to add custom columns of their own to an entity.

..

 

[vanderghast]

Each statement inside a transaction is subjected, individually, to the
enforced rules and follow the same Relational Data Integrity than if the
statement would have been outside the transaction. A transaction does NOT
disable Relational Data Integrity (by default); it just offer isolation of
the modifications in progress, it does not allow insertion of invalid data,
invalid because of an enforced relation. A transaction does not skip over
relations, at least, neither in Jet neither in MS SQL Server. Same for
statements executed inside a trigger: by default, they have to be validated,
individually. Which commercial database engine don't do the same?


Vanderghast, Access MVP

 

[Harlan Messinger]

You never heard of Transactions?


You never heard of relationships enforced via triggers in sql server?

If you read anything about normalisation they usually say you normalise
1:1 relationshiops into one table.
However.
It's a common pattern to have two tables related 1:1 in packages that
allow the user to add custom columns of their own to an entity.

That case implies the the entity was perfectly happy to have rows in it
to which there were no corresponding rows in the table with the custom
columns, since the table with custom columns didn't even come along
until the user in question decided he wanted to associate some data of
his own with the data in the existing entity. In other words, in that
case, the relationship (in ER-speak) is inherently 1:0..1, not 1:1, once
the custom table is added.

 

[Andy O'Neill]

That case implies the the entity was perfectly happy to have rows in it to
which there were no corresponding rows in the table with the custom
columns, since the table with custom columns didn't even come along until
the user in question decided he wanted to associate some data of his own
with the data in the existing entity. In other words, in that case, the
relationship (in ER-speak) is inherently 1:0..1, not 1:1, once the custom
table is added.

Jeez.
Custom COLUMNS.
The pattern has 2 tables per entity, always..
Base table 1:1 User columns table

 

[Andy O'Neill]

Each statement inside a transaction is subjected, individually, to the
enforced rules and follow the same Relational Data Integrity than if the
statement would have been outside the transaction. A transaction does NOT
disable Relational Data Integrity (by default); it just offer isolation of
the modifications in progress, it does not allow insertion of invalid
data, invalid because of an enforced relation. A transaction does not skip
over relations, at least, neither in Jet neither in MS SQL Server. Same
for statements executed inside a trigger: by default, they have to be
validated, individually. Which commercial database engine don't do the
same?


Vanderghast, Access MVP

DB2 implements relationships rather differently to SQL server.
Oracle, you can tell it to enforce constraints after commit.

SQL server.
For 1:1 joy.
Enforce in one direction using a constraint.
Enforce the other direction using an after trigger.
Place your insert of both tables in a transaction.
Use the trigger to invalidate the transaction.

Get it?

 

[vanderghast]

In SQL Server, it is only a 1:0..1 which is strictly enforced, by default. A
trigger can always do something in extra, it goes as people wishes, such as
someone can use a trigger to maintain a computed value (a sum), if that is
what they really want, but that is not strictly speaking a RDI.

EVEN with the process you mention, you have to be careful to start with
inserting data in the LEFT table, because you won't be able to start
appending data in the RIGHT table (you will bet the error: "statement
conflicted with FOREIGN KEY constraint") with a value not yet in the left
one. So that clearly shows the way we look at this relation MATTERS, since
it is far from being symmetric.


Vanderghast, Access MVP

 

[Harlan Messinger]

Jeez.
Custom COLUMNS.
The pattern has 2 tables per entity, always..
Base table 1:1 User columns table

Oh, I see. You mean, the table is there, possible empty except for the
primary/foreign key, when the package is acquired by the user, with the
1:1 relationship already established and enforced. That makes sense.

 

[Andy O'Neill]

Andy O'Neill wrote:

Oh, I see. You mean, the table is there, possible empty except for the
primary/foreign key, when the package is acquired by the user, with the
1:1 relationship already established and enforced. That makes sense.

Yes.
The advantage is that the clients only stick their rubbish in "their" table.
As soon as you give them something they can customise you can guarantee a
lot of clients will do silly stuff.
So your reset to factory settings process is simpler.
A more subtle advantage is that it makes it harder for them to go direct
into your tables and dodge business logic.
Usually I would strongly recommend avoiding "clever" stuff.
If I do it easy then maybe on Friday 5pm when the inevitable support call
comes in, I still get down the pub.

 

[Andy O'Neill]

In SQL Server, it is only a 1:0..1 which is strictly enforced, by default.
A trigger can always do something in extra, it goes as people wishes, such
as someone can use a trigger to maintain a computed value (a sum), if that
is what they really want, but that is not strictly speaking a RDI.

EVEN with the process you mention, you have to be careful to start with
inserting data in the LEFT table, because you won't be able to start
appending data in the RIGHT table (you will bet the error: "statement
conflicted with FOREIGN KEY constraint") with a value not yet in the left
one. So that clearly shows the way we look at this relation MATTERS, since
it is far from being symmetric.


Vanderghast, Access MVP

A relatonship is not a constraint, a constraint is a detail of
implementation for modelling a relationship.

A trigger is just as much a part of sql server as a constraint.
They are both ways of implementing your data model.
2 triggers might work in which case the order you inserted wouldn't matter.

My original point was:
It doesn't matter which of the two tables you put the foreign key (of the
other one ) in.
Which one you insert first would change dependent on how you implement it
but either way will work.
This was, if you recall, in the context of comparing to a 1:m relationship
where the foreign key must go in the table representing the many side..

You argued all this because you couldn't accept my example could be
purposely symmetric.

 

[vanderghast]

I argued about the 1:1 relation ( as with jet and with MS SQL Server) is not
symmetric, not that it cannot be made symmetric, so by lack of symmetry, the
way we look at it matters. And I still stand behind this claim.


Vanderghast, Access MVP

 

[vanderghast]

Also note that using two triggers is not a proper implementation in every
case since it would imply that at some point the other columns than the
(single) one concerned by the relationship to be temporary filled by nulls
or by their default values, which would fail in the specific case where a
not nullable column has no default value. So even with two triggers, the
process can be far from being symmetric, and not to say quite unusual
anyhow.

Vanderghast, Access MVP