Compound key

[Peter Warde]

Can you use a CMR field - as well as a CMP field - as part of a compound primary key class?

[Nileesha Bojjawar]

Hi Peter,
Few points as my argument for the compound key you want to create using a CMP and a CMR field.

According to the rules for the Primary key, A compound key class must be made up of fields that aredefined as the persistent fields in the bean class, and the feilds in the bean class must have public accessors.

you cannot use ejbCreate to access your own persitent relationships, you have to wait until ejbPostCreate()

If t the end of ejbCreate you primary key is not completely established the container wont do the create!Remeber the rule, if you use a compund key then all the fields that make up the key must have validvalues by the end of ejbCreate()

I guess then this is the situation where u cannot get CMR till ejbcreate completes and u cannot get thh PK till u get ur CMR and till u get you PK u cannot complete ejbCreate() .. tough situation for the container.

Moreover IMHO, a primary key comprising of a CMP and a CMR is a bad design of the database or your application.
Point me if i am wrong.

Regards

[ October 12, 2004: Message edited by: Nileesha Bojjawar ]
[ October 12, 2004: Message edited by: Nileesha Bojjawar ]

[Peter Warde]

Hi Nileesha

I take your point about the primary key needing to be established before the end of ejbCreate() and that a CMR field cannot be done until after the the bean has been created. I didn't see it.

However designing a db you can often use a 2 table columns to create a primary key one of which is also the foreign key in a relation with another table. Why is this bad db design and how can it be created in beans.

Peter

[Nileesha Bojjawar]

Hi Peter,
If your DB design is like that then i guess you will have to use BMT, unless EJB 3.0 comes up wih any magical solution.
Regarding the design, I am not clear myself, just a hunch that it may not be a good design as the scenarios i worked on didn't have them, I worked mostly on the OO database design using OID's etc, and other normalized databases, but did not come across and Compound primary key which is the foriegn key(May be my experience is not vast).
Thinking about it logically,
A relationship in two table can be established through a primary key - foriegn key pair and now if the establishment of the Primary key of one table is depending on the foriegn key of another table those 2 tables become difficult to populate unless one of them can be null( which will not be the case).
Just beating my head round, to be honest.
May be you can give me an example where and how you achieved a compound key comprising of a forieng key and the primary key of a table.

Regards,

[Peter Warde]

Hi Nileesha,

Here's and example of a compound primary key composed of a table column and a foreign key in a relation with another table.

Suppose you had 2 tables - city and state - that modelled data for all the world. There are many cities in the world that have the same name eg London, Ontario, Canada and London, South East, UK so the name London is not sufficiently unique to a be a primary key candidate whilst being semantically correct. In this case you have would use city and state eg London, Ontario and London, South East as your compound primary key in the city table and state as the primary key in your state table. The SQL code would look like this:

CREATE TABLE State
State VARCHAR 100 UNIQUE,
CONSTRAINT State_pk PRIMARY KEY (state)

CREATE TABLE City
City VARCHAR 100,
State VARCHAR 100
CONSTRAINT state_pk PRIMARY KEY (City, State)
CONSTRAINT State FOREIGN KEY REFERENCES State

(I'm not sure if the SQL is perfect, but you get the idea). The problem is how to create this compound key and relationship in ejb. Any thoughts?

Cheers

Peter

[Nileesha Bojjawar]

Any thoughts please?

[Nileesha Bojjawar]

Kathy, I can see you posting around some topics, so guessed you must be around.
Please help us with this Compound key, is it achievable in EJB 2.0/1

Thanks

[Sandesh Tathare]

Nileesha,

At outset let me thank you for posting so many replies to forum. Gr8 Job!

You say:

Thinking about it logically,
A relationship in two table can be established through a primary key - foriegn key pair and now if the establishment of the Primary key of one table is depending on the foriegn key of another table those 2 tables become difficult to populate unless one of them can be null( which will not be the case). Just beating my head round, to be honest.

Well that's not the case. In example quoted of State and City, one can insert records into State table first and subsequently into City table. There's nothing bad in such database design.

I will quote another example of say 2 entities namely Doctor and Patient have M:N (Many to Many relationship). In such a case, we introduce an intersection entity say Doctor_Patient_Mapping. The primary key of which is a composite primary key i.e. doctor_id, patient_id. And it references to primary key of respective tables i.e. doctor_id of Doctor and patient_id of Patient table.

Your another point about ejbCreate makes sense. Let's try it out.

[Vagner Freitas]

Peter and Sandesh,

I think if you is using CMR entity beans and you have many-to-many relationship you don't need introduce an intersection entity. It is container responsability. Only you have to do is to define the relationship in DD <ejb-relation>.
And if I had the situation showed by Peter I would model data like:

CREATE TABLE State
State_Id INTEGER (Sequence),
State VARCHAR 100 UNIQUE,
CONSTRAINT State_pk PRIMARY KEY (State_Id)

CREATE TABLE City
City_Id INTEGER (Sequence),
City VARCHAR 100,
State_Id INTEGER,
CONSTRAINT City_pk PRIMARY KEY (City_Id)
CONSTRAINT State_Id FOREIGN KEY REFERENCES State

What do you think?

Vagner

[Peter Warde]

Dear Vagner

One of the point I'm trying to make is not about using an integer sequence as a primary key, but using the the city and the state. An integer sequence will give you uniqueness, but it won't ensure that the data is semantically correct. For example you could have for city_id and city and state the following: 45 London Ontario, 46 London Ontario, 47 London Ontario. But if you make city and state the primary keys then the table can only have one London Ontario which is meaningfully correct.

The problem is how to do this using entity beans without using id sequences.


Peter

[Peter Warde]

Sandesh,

You are right. It's quite easy to populate such a datebase with such primary and foreign keys in SQL.

Usually the tables are created first without any foreign keys, then populated with data and then the relationships and foreign keys are added using SQL ALTER TABLE statements. If you look at a database backup and restore tools eg Postgres it will usually follow this proceedure.

Peter