Understanding RI model

[markros]

Hi everybody,

I'm guessing I have an issue with Database RI model, because I'm working with the old application.

Ok, here is the question.

Is it possible conceptually this model

Child1 FKField
Parent PKField1

Child2 FKField2
Parent PKField2

---------------------------
In my case I have a table that had Name field as a PK and had children using name as an FK.

I added a new identity field for this table to set up relation with another new table. I got a problem with my older form, so I had to make the name field primary again.

But does it mean I was wrong and I can not have different tables related to my table based on different fields?

Could you please clarify this confusion for me?

Thanks in advance.

 

[JoeAtWork]

I don't fully grasp your table structures, but it's normal to have a primary table with multiple child tables.

The "problem with your older form" could be many things, and without knowing anything about it (like if it is VB, Access, a .NET Windows project, and if it uses some sort of binding or you have code to update the fields, etc.) that is impossible to diagnose. But I would expect that if you changed you database schema, you would have to change any existing code that depended on the old schema. For instance if your form is in Access and it uses a query as the datasource, if that query does not contain the new identity column then it would probably make the form uneditable.

 

[markros]

The database is Visual FoxPro database. The table Manufacturers had name field among other fields. This field was used in another table as a foreign key. Today I learned who developer of this application was. I don't know what he was thinking designing this database.

I added a new primary key to Models table and to Manufacturers table, because I needed to have references to them in another table. However, I found, that the form that referenced the old table (with link to Manufacturers through the Name) produced an error. I found a way to fix this error, but I'm wondering now - would I have to change structure of my old tables and use Keys instead of names.

Thus my question - in normal RI model is it permissible to have relations based on different fields, e.g. based on Name with my old tables and based on ID for my new tables.

 

[r937]

yes it is permissible, but if the name isn't the PK, then it must at least be a unique key in order to be referenced by the name FK

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JoeAtWork]

Technically I think you are breaking one of the rules normalization if you have two different primary keys. It also can give you maintenance headaches.

Another option is to make the primary key a combination of the Name and Identity columns.

 

[r937]

no table can have two primary keys

it is perfectly okay for a table to have a primary key that is referenced by some foreign key, as well as a unique key that is also referenced by some other foreign key

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

I believe the Name in the aforementioned table is unique, but I can verify that.

In any case, what my course of actions now should be?

Should I try to get rid of name field in all old tables that had it and switch to using ID field as a reference? This would require going through all forms in this application and determine an impact of such change.

Or leave the situation as is?

Thanks.

 

[r937]

i would leave it

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon