Complaint about my code-generation strategy to implement many-to-many associations in relational databases

Tweet about this on TwitterShare on FacebookBuffer this pageShare on RedditShare on LinkedInShare on Google+Email this to someone

I got a complaint about the translation of many-to-many UML associations to SQL tables in my UMLtoSQL online service .

More specifically, the complaint was that in the translation of this UML class diagram: customer

the Recommends association was implemented as an independent table with an autoincrement id field as the PK in it. This is the excerpt of the translation corresponding to this association:

 -- Table for storing the data of  Recommends
  CREATE TABLE Recommends_rt(
    id INTEGER(5) NOT NULL AUTO_INCREMENT,
    -- Attribute referencing the customer
    customer INTEGER(5) NOT NULL ,
    -- Attribute referencing the book
    book INTEGER(5) NOT NULL ,
    CONSTRAINT pk_Recommends PRIMARY KEY (id),
    CONSTRAINT u_Recommends1 UNIQUE (customer,book)
  );

  ALTER TABLE Recommends_rt
    ADD CONSTRAINT fk_RecommendsToCustomer_customer FOREIGN KEY(customer)
      REFERENCES Customer_rt(id)
      ON DELETE restrict
      ON UPDATE cascade;

  ALTER TABLE Recommends_rt
    ADD CONSTRAINT fk_RecommendsToBook_book FOREIGN KEY(book)
      REFERENCES Book_rt(id)
      ON DELETE restrict
      ON UPDATE cascade;

This is a fully conscious decision. I know that the “traditional” way of implementing M:N associations is with a composite PK consisting in the union of the PKs of each participant class (in the example, customer+book would be the PK). However, I think the strategy I implemented in my service is better for two reasons:

  • It enforces the same data integrity constraints: according to the semantics of associations in conceptual modeling languages like ER or UML (well, in fact UML 2.0 has the property isUnique that can be used to specify a different behaviour but IMHO this is not useful for data modeling), a customer cannot recommend twice the same book. This constraint is enforced by the Database Management System (DBMS) when is the PK. However this constraint is also enforced in my solution since the generator adds a unique constraint in the table that has the same effect
  • It is a more pragmatic solution (in fact, I decided to adop this solution after discussing with several colleagues working as SE in different companies). The main problem with composite keys are that: 1- you need to propagate them to other tables (e.g. if Recommends was an association class and we would allows external users to comment on the recommendation, then the Comment table would need a composite FK to recommends to relate both concepts) and this is cascading effect (imagine the length of a PK if a many-to-many association class participates in another many-to-many association), 2 – implementing the GUI for the application gets more complex since we always need to take into account the possibility of having tables with composite PKs

What do you think? What strategy do you use for this? In general, do you prefer to be a pragmatic or a purist?

Btw, the complaint continued by saying “you’re clearly pandering TO the RoR/Django crowd who have forgone good design IN the NAME OF make their ORM tools easier TO WRITE” . RoR/Django fans, feel free to react!! (obviously I disagree with the comment: to me the design quality is better because we keep the semantics and offer a simpler solution)

Tweet about this on TwitterShare on FacebookBuffer this pageShare on RedditShare on LinkedInShare on Google+Email this to someone
Comments
  1. Anonymous
  2. rafael
  3. Anonymous
  4. Anonymous
  5. jordi
  6. Lee Riemenschneider
  7. Alejandro

Reply

Your email address will not be published. Required fields are marked *