CODEWITHSUNDEEP
mysqloracle-databaseentity-frameworkrdbms
LukeeTaylorr
LukeeTaylorr

Reputation: 37

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.

Upvotes: 1

Views: 414

Answers (1)

1000111
1000111

Reputation: 13519

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.

Upvotes: 1

Related Questions