[lfoulkrod]

Let's say I have two entities, A and B that look like:

Entity A
--------
ID - int primary key
B_ID int foreign key references Entity B (ID)

Entity B
--------
ID - int primary key
A_ID int foreign key references Entity A (ID)

The relationship can be established either when you create or update entity A or create or update entity B.

My problem is that with this design, I have to know that when I update entity A to relate to entity B, I also have to update entity B to reflect this relationship. Same thing when I update entity B first.

Is there a better design strategy for this?

[r937]

Originally Posted by lfoulkrod

Is there a better design strategy for this?

can't answer that question without knowing what you're trying to do

having A relate to B and also B relate to A is very, very unusual

perhaps you could either (1) tell us your real tables and columns, so that we could understand the relationships better, or (2) give examples of several rows of actual data so that we can understand the relationships better

[lfoulkrod]

The real tables are types of assets, a computer for example. Table A represents a legacy asset, one that will be replaced by table B, the new asset. So they would look like:

Legacy Asset
-------------
LAID int primary key
Manufactuer
Model
etc...

New Asset
-----------
NAID int primary key
Manufacturer
Model
etc....

Now when you enter a new asset into the system you can associate it with a legacy asset, but the same is true for when you enter a legacy asset in the system, you can associate it to an existing new asset. The relationship between assets is one to one.

[r937]

Originally Posted by lfoulkrod

The relationship between assets is one to one.

ah, well, that's okay then

[lfoulkrod]

It still seems kind of funky to me to have to update both tables when I create an association between the two entities. It seems more natural and less error prone to have the relationship defined in a single place. Right now it is:

LegacyAsset
------------
LAID int primary key
NAID int foreign key (NewAsset.NAID)

NewAsset
---------
NAID int primary key
LAID int foreign key (LegacyAsset.LAID)

[blindman]

Originally Posted by r937

ah, well, that's okay then

It doesn't get my stamp of approval. How do you ensure that the correct records are pointing at eachother? Can an asset be replaced by more than one asset? Can an asset replace more than one asset? For instance, a fax and a printer could be replaced by a single faxprinter. How would that be represented.
I do not think this is a good database design.

[lfoulkrod]

The current requirements is that one asset is replaced by another single asset. A fax by a fax, a printer by a printer. I didn't think it was good database design, that's why I created this thread and asked for suggestions on the correct design, do you have any suggestions?

[andrewst]

My syggestion would be:

LegacyAsset
------------
LAID int primary key

NewAsset
---------
NAID int primary key
LAID int foreign key (LegacyAsset.LAID)

i.e. there is only one relationship, not two.

To query which new asset replaces old asset 123

select naid from newasset where laid=123;

To query which old asset was replaced by new asset 456

select laid from newasset where naid=456;

What more do you need?

[blindman]

Originally Posted by lfoulkrod

The current requirements is that one asset is replaced by another single asset. A fax by a fax, a printer by a printer. I didn't think it was good database design, that's why I created this thread and asked for suggestions on the correct design, do you have any suggestions?

I suggest you reevaluate your requirements, because that seems pretty ignorant of real-life business rules to me.