i have two entities. I want the data between them one to many and many to one. But not many to many.How can i implement this using ER diagram and then into relational tables.


well, two one-to-many relationships of two entities where each entity is related to the other one usually makes up a many-to-many relationship between them.

There is also a possibility that both one-to-many relationships are treated separately. In such case the primary key of one entity becomes foreign key of the other entity. To express a strict one-to-many relationship these foreign keys are non-identifying that is, they are not part of the another entity's primary key.

The attached ERM shows such a relationship.

On a relational database the tables could be created as follows:

create table A_Entity(aID int not null, adata char, bfk int, primary key (aID));

create table B_Entity(bID int not null, bdata char, afk int, primary key (bID), foreign key afk references A_Entity);

alter table A_Entity add foreign key bfk references B_Entity;

One should consider that alter table is necessary because of the recursive foreign key references. There is no forward declaration on SQL. I made use of standard SQL/99 alter-statement. Depending on your database alter-statement may vary.

-- tesu

i am xyzxyzxyz only. I want a third table to relate them as these are two independent entities.

Sorry, I don't understand what you meant. Pls, can you rewrite again.

-- tesu

Btw, did I meet your conception of two one-to-many relationships?

Ah, I see. So you own two accounts here?