I don't completely understand this. Suppose you have these tables:
Person (PK PersonName)
TraveledTo (PersonName, CountryName) => PK spans all
Country (PK CountryName, CountryPopulation)
Suppose you want to create a form to add/remove people and specify the countries they have visited, with a main datasource that includes the above tables in the sequence shown. It doesn't seem possible (or easy) to allow the user to add new countries with this form and I'm not sure why there is this technical limitation. The form can only reference existing countries on the fly - a different form is needed to add them. Why can't InfoPath be smart and know to insert a new Country into the database if details about it, like CountryPopulation, are specified? If no details are specified, then InfoPath can assume an existing County is being referenced.
It also seems odd (inconsistent) that the rules for Insert are different than the rules for Update. In the example above, if it has been recorded that...
traveled to Country India
has Population 1,000,000
...I am able to update India's population from InfoPath even though the relationship from TraveledTo to Country does not have a PK on the left side. Why can I update the Country table but not insert a new tuple in it?
Thanks for your reply and your interest, justinm!
I am going to re-state your questions as I interpret them:
1) When I add the "Countries" table to my data source, and relate the "TraveledTo" table to the "Countries" table on the "CountryName" columns in each table, why doesn't InfoPath infer the schema such that "Countries" is a repeating node?
2. Why does InfoPath tell me I may have a many-to-one relationship when I've followed the "Primary Key on the left at least once in every relationship between two tables?"
Let's start with #1:
The "Countries" table has a primary key constraint defined on the "CountryName" column. In the data source, the "TraveledTo" table is related to the "Countries" table on the "CountryName" columns in each table. This relationship requests, "for each record in the 'TraveledTo' table, fetch the record in the 'Countries' table that has an identical 'CountryName' value." But, since "Countries"."CountryName" is a primary (unique) key, there will always exist 0 or 1 record(s) that satisfy the relationship. As a result, InfoPath infers a schema that reflects the fact that the query may return no more than one record from the "Countries" table in the context of the relationship. Therefore, the node is non-repeating, and you can't add more countries.
In the context of the relationship between tables, the country name is already determined by the value of the "TraveledTo" record. Therefore, the relationship indicates that any countries that would be added would have the same country name. But such an addition would violate the primary key constraint of the "Countries" table. If, however, you remove the unique constraint from the "Countries"."CountryName" column, then InfoPath will infer a repeating schema node, and you can add new countries, because no constraint will be violated. It's not so much a matter of being smart about manipulating the data source, but rather about adhering to standards that provide structure for InfoPath's interaction with the database.
Now for #2:
When you define a multiple-column primary key, no subset of the primary key is guaranteed to be unique. In the case of the "TraveledTo" table, each of the "PersonName" and "CountryName" columns can have duplicate values. As a result, when you define a relationship between the "TraveledTo" table and another table via the "CountryName" column, there may be many records in the "TraveledTo" table that map to one record in the related table.
When such a many-to-one relationship is present, querying the data source will result in an individual record being displayed and editable in the form in more than one section at once. If the user edits the record in multiple places in the form, conflicts must then be resolved at submit time.
Software Development Engineer in Test
If you're interested in making InfoPath forms work with highly relational data through ADO.NET datasets, be sure to check out this article by Michael Hofer.
Question. I am just looking to see if this is the right tool for me. I am looking to do no programming and have users INSERT answers to a questionaire. so I would have a question table, an answers table, and a dropdown table that would give a selection of possible answers for each question. Only the answers table would grow. I could quickly code this in VB6 without issue using SPs but could this tool do this out of the box? do you have any examples of this being done? I know of no way to do this with your tool without going into code. how much code would this require
PingBack from http://edgar.newssiteworld.com/databaseonetomanyrelationship.html
PingBack from http://fixmycrediteasily.info/story.php?id=4151
PingBack from http://topalternativedating.info/story.php?id=10135
Nice post. Here’s a tutorial that shows how you can easily build an online database-driven web application with a parent-child table relationship, without codingblog.caspio.com/.../creating-one-to-many-relational-datapages