Creating tables

[pczafer]

Hi All,

I hope someone can help. I confused with one part of my coursework.

"For each booking there are a number of payments. There are three types of payment: the deposit (usually made when the holiday is booked), the balance (the rest of the cost, which is usually paid up about one month before the trip starts), and an optional insurance payment. The database should record the type, date and amount of each payment. There is only one payment of each type per booking."

How many tables should i create here? 4? like Paymnet, Deposite, Balance and insurance or

should i make only one table Payments? Payment has type,date and amout as its attribute but i am not sure with others.

[pczafer]

any idea anyone?

[debasisdas]

You need to store the payment details in a single table with a payment type flag that specifies what type of payment that was.

[BitBlt]

And don't forget to include that payment type flag as part of the primary key, to ensure that there's only one of each type per booking!

[pczafer]

Ok i created one table as below

PPAYMENT....PMT)
Key Type........Optionally......Column Name
pk, fk1.....................*...........booking_code
................................*...........type
................................*...........payment_date
................................*...........amount

CREATE TABLE payments
(booking_code VARCHAR2(4) NOT NULL,
type VARCHAR2(25) NOT NULL,
payment_date DATE DEFAULT SYSDATE NOT NULL,
amount NUMBER(7,2) NOT NULL,
CONSTRAINT "PMT_BOOKING_CODE_PK" PRIMARY KEY ("BOOKING_CODE") ENABLE);

ALTER TABLE payments ADD CONSTRAINT "PMT_BKG_ CODE_FK" FOREIGN KEY ("BOOKING_CODE") REFERENCES bookings ("CODE") ENABLE;

i can only insert one payment for each bookings?

and am not sure for the TYPE's Optionally? i made as mandatory but Insurance payment must be Optional!

any help with this?

[pczafer]

any idea anyone?

[debasisdas]

Your PK should be based on the combination of

booking_code and type.

[pczafer]

thanks already done it that way...

[crishjeny]

make master table for paymenttypes and save all four Paymnet, Deposite, Balance and insurance in this table and use its foreignkey to insert in another table where you save other data related this payment