0

In my Database, I have 3 tables about different kind of asset : house and ground, locative buildings, and stocks.

Another table is about borrowing.

Now I have to link assets and borrowing. The rules are : - one borrowing can optionnaly linked to only one asset. - one asset can optionnaly linked to one or more borrowings.

I started with this database design :

CREATE TABLE CUSTOMER
(
  CUSTOMER_ID INT NOT NULL,
  [...] 

  CONSTRAINT PK_CUS_ID PRIMARY KEY (CUS_ID)
)

CREATE TABLE ASSET1
(
  ASSET1_ID   INT NOT NULL,
  CUSTOMER_ID INT NOT NULL,
  [...]

  CONSTRAINT PK_ASSET1_ID PRIMARY KEY (ASSET1_ID),  
  CONSTRAINT FK_ASSET1_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE ASSET2
(
  ASSET2_ID   INT NOT NULL,
  CUSTOMER_ID INT NOT NULL,
  [...]

  CONSTRAINT PK_ASSET2_ID PRIMARY KEY (ASSET2_ID),  
  CONSTRAINT FK_ASSET2_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE ASSET3
(
  ASSET3_ID   INT NOT NULL,
  CUSTOMER_ID INT NOT NULL,
  [...]

  CONSTRAINT PK_ASSET3_ID PRIMARY KEY (ASSET3_ID),  
  CONSTRAINT FK_ASSET3_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE BORROWING
(
  BORROWING_ID INT NOT NULL,
  CUSTOMER_ID  INT NOT NULL,
  ASSET1_ID    INT     NULL,
  ASSET2_ID    INT     NULL,
  ASSET3_ID    INT     NULL,
  [...]

  CONSTRAINT PK_BORROWING_ID PRIMARY KEY (BORROWING_ID),

  CONSTRAINT FK_BORROWING_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_BORROWING_ASSET1   FOREIGN KEY (ASSET1_ID  ) REFERENCES ASSET1   (ASSET1_ID  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_BORROWING_ASSET2   FOREIGN KEY (ASSET2_ID  ) REFERENCES ASSET2   (ASSET2_ID  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_BORROWING_ASSET3   FOREIGN KEY (ASSET3_ID  ) REFERENCES ASSET3   (ASSET3_ID  ) ON DELETE NO ACTION ON UPDATE NO ACTION,

  CONSTRAINT CHK_BORROWING CHECK 
  (
      ((ASSET1_ID IS     NULL) AND (ASSET2_ID IS     NULL) AND (ASSET3_ID IS     NULL))
   OR ((ASSET1_ID IS NOT NULL) AND (ASSET2_ID IS     NULL) AND (ASSET3_ID IS     NULL))
   OR ((ASSET1_ID IS     NULL) AND (ASSET2_ID IS NOT NULL) AND (ASSET3_ID IS     NULL))
   OR ((ASSET1_ID IS     NULL) AND (ASSET2_ID IS     NULL) AND (ASSET3_ID IS NOT NULL))
  )
)

It is working and referential integrity seems to be OK.

But I already know that I will have to add more assets in my database in the future and the CHK_BORROWING will become a monster.

My question : Is there a better, cleaner, simpler solution ?

Thank you

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
NMD
  • 135
  • 11
  • Does you ASSET_ schema contains different field structure – Amit Kumar Sep 30 '16 at 10:31
  • The key to your question is "different kinds of assets". If this can be rephrased as "different subclasses of assets", then the answer may be here: http://stackoverflow.com/questions/13749525/relational-database-design-multiple-user-types/13752304#13752304 – Walter Mitty Sep 30 '16 at 11:27
  • @AmitKumar Yes each ASSET table has different columns – NMD Sep 30 '16 at 11:59
  • @WalterMitty The application is already in production for years and today each asset table is very different. Subclassing assests is a very interesting solution but the time required to modify the application is too important. Sorry but I can not consider it. – NMD Sep 30 '16 at 12:05
  • If you can't change the database design, what kind of solution are you asking for? – Walter Mitty Sep 30 '16 at 12:13
  • @WalterMitty Sorry if i'm not clear.I do not master English well. Todays each asset table exists with very different structure. The borrowing table exists too but not the link to assets. I was about to add ASSET1_ID, ASSET2_ID, ASSET3_ID, Foreign keys and CHK_BORROWING check constraint to the borrowing table. This has a small impact on my application. I'm not very satisfied. So I wondered if another solution exists without changing assets tables. – NMD Sep 30 '16 at 12:34
  • 1
    If you used Class Table Inheritance, you could create a table, let's call it ASSETS_GENERIC. Every asset, regardless of type, would have an entry in this table. Now you can make a contrained linkage between Borrowing and Assets_generic in the usual way (REFERENCES). And now you make a connection between ASSETS_GENERIC and each of the ASSETx tables. It's not clear whether this is any better that your plan. – Walter Mitty Oct 01 '16 at 12:17
  • After thinking more about your proposal of "Subclassing assets", I thought indeed that I could create a generic asset table. The changes is minor and the solution is ready for future when I will add more assets. I'm sorry not to have caught your first proposal. I did not have enough experience. Could you add an answer tha I will accept ? – NMD Oct 01 '16 at 17:22

1 Answers1

0

As suggested by Walter Mitty the solution is Class Table Inheritance.

If you used Class Table Inheritance, you could create a table, let's call it ASSETS_GENERIC. Every asset, regardless of type, would have an entry in this table. Now you can make a contrained linkage between Borrowing and Assets_generic in the usual way (REFERENCES). And now you make a connection between ASSETS_GENERIC and each of the ASSETx tables.

NMD
  • 135
  • 11