Search code examples
mysqloracle-databaseentity-frameworkrdbms

Relational Database Design - Create an Entity Class for Payment Type?

I am currently designing a relational database for a project I am working on.

I am unsure of the best way to do it, I will try to explain as best as possible.

If I have an Entity: 

SALE(SALE_ID, SALE_TYPE, SALE_AMOUNT, CUSTOMER_ID)

and,

PURCHASE(PURCHASE_ID, PURCHASE_TYPE, PURCHASE_AMOUNT, CUSTOMER_ID)

If the PURCHASE_TYPE and SALE_TYPE in each entity can only be Cash OR Card.. would if be worth creating a new Entity called for E.g. Transaction Type with this structure

TRANSACTION_TYPE(TYPE_ID, PAYMENT_TYPE)

Then replacing PURCHASE_TYPE and SALE_TYPE with TYPE_ID as a foreign key in the other 2 entity's?

Thanks in advance for any responses :) I hope I explained it well enough and used the correct terminology.

Solution

  • As you mentioned the payment types are:

    • Cash
    • Card

    So you can prefer having the datatype of SALE_TYPE or PURCHASE_TYPE AS ENUM.

    If the number of payment types would have been larger then it would have been better to keep the types in a separate table like you said and replace the type field with the foreign key constraint.

    So having enum data type your SALE table would look like:

    CREATE TABLE `SALE` (
`SALE_ID`  int(11) NOT NULL AUTO_INCREMENT ,
`SALE_TYPE`  enum('Cash','Card') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`SALE_AMOUNT`  decimal(18,4) NULL DEFAULT NULL ,
`CUSTOMER_ID`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`SALE_ID`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
;

    Same should hold for PURCHASE table.

    Note that SALE_TYPE enum('Cash','Card') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

    In a word, I would go for keeping the original structure having the slight change in the data type of your *_Type field i.e. ENUM.