File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Compound key

 
Peter Warde
Ranch Hand
Posts: 71
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you use a CMR field - as well as a CMP field - as part of a compound primary key class?
 
Nileesha Bojjawar
Ranch Hand
Posts: 85
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Ranch Hand
    Posts: 71
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 85
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 71
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 85
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Any thoughts please?
     
    Nileesha Bojjawar
    Ranch Hand
    Posts: 85
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 82
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 85
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 71
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Ranch Hand
    Posts: 71
    • 0
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
     
    I agree. Here's the link: http://aspose.com/file-tools
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic