Table needs check!!

arayici · #1 (**permalink**) 04-24-11, 18:05

Hi Everyone,

I created a table for the scenario below.

Quote:

"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.
"

Code:

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;

booking_code is PK,FK1

I am not sure about the type, i made as mandatory(NOT NULL) but Insurance payment must be Optional?

can anybody check this and give a feedback?

r937 · #2 (**permalink**) 04-24-11, 21:29

Quote:

Originally Posted by arayici

I am not sure about the type, i made as mandatory(NOT NULL)

this is correct

it would be an error to allow a payment of an unknown type

Quote:

Originally Posted by arayici

but Insurance payment must be Optional?

well, that's pretty simple --

if there is no insurance payment, then there is no row in the payments table for it

arayici · #3 (**permalink**) 04-25-11, 02:39

Quote:

Originally Posted by r937

well, that's pretty simple --

if there is no insurance payment, then there is no row in the payments table for it

But when i try to insert 'Deposit' and 'Insurance' payment for the same BOOKING i get an error as booking_code is PK,FK??

Quote:

INSERT INTO payments
(booking_code, type, amount)
VALUES ('B001', 'Deposit', 400);

INSERT INTO payments
(booking_code, type, amount)
VALUES ('B001', 'Insurance', 200);

Quote:

ORA-00001: unique constraint (ORACLE_SQL.PMT_BOOKING_CODE_PK) violated

so how i will insert multiple payments for the same booking?

healdem · #4 (**permalink**) 04-25-11, 04:45

Quote:

Originally Posted by arayici

But when i try to insert 'Deposit' and 'Insurance' payment for the same BOOKING i get an error as booking_code is PK,FK??

so how i will insert multiple payments for the same booking?

according to your requirement

Quote:

There is only one payment of each type per booking.

so you need to reread the requirement and work out what types of payments a person may make against a booking

payments can / could be
none
deposit
balance
total amount

reading your brief there is no need to allocate a payment against an element of the invoice, nor are you allowed to make more than one of the smae type of payment, so you cannot take two deposits, you cannot take more than one balance payment and so on.

arayici · #5 (**permalink**) 04-25-11, 06:51

Quote:

Originally Posted by healdem

payments can / could be
none
deposit
balance
total amount

reading your brief there is no need to allocate a payment against an element of the invoice, nor are you allowed to make more than one of the smae type of payment, so you cannot take two deposits, you cannot take more than one balance payment and so on.

Yes you are right. we can't add two deposit or two balance or two Insurance payment for the same booking. but we should be insert one each.

So is that my mistake to use the booking_code as a PK?

as PK must be unique in the table, i can insert only one type of payment for per booking?

arayici · #6 (**permalink**) 04-25-11, 12:33

any idea anyone.
I am stuck here.....

healdem · #7 (**permalink**) 04-25-11, 16:06

so what is the problem?
what steps have you taken to diagnose the problem?
what solutions have you got to resolve the problem?

you are saying you are getting a unique constraint violation
so is the problem the way you have defined that constraint
or are you missing something in your design

put simply does your design allow for one payment per booking per payment type.

futurity · #8 (**permalink**) 04-25-11, 17:39

Quote:

Originally Posted by arayici

So is that my mistake to use the booking_code as a PK?

Yes.

Quote:

as PK must be unique in the table, i can insert only one type of payment for per booking?

Yes. This is your primary key, but this is not what you've implemented in your table.

arayici · #9 (**permalink**) 04-25-11, 17:46

Quote:

Originally Posted by healdem

put simply does your design allow for one payment per booking per payment type.

That what was I trying to do! I think I confused myself here.

my tables like below.

HTML Code:

BOOKING (BKG)
KeyType	   Optionally	Column Name
  pk	      *	          booking_code
	      *	          booking_date
  fk          *	  trp_code
  fk2	      *	          cet_refecence_no




PAYMENT (PMT)
Key Type	  Optionally	 Column Name
  pk, fk1	      *	       bkg_booking_code
	              *	       type
	              *	       payment_date
	              *        amoun

I have 1:M non-transferable relationship between Booking ant Payment

I am thinking the bkg_booking_code must be the PK for the payment table and its also the FK for the same table. So if my design wrong here how would i create the relationship between these two tables?

healdem · #10 (**permalink**) 04-25-11, 18:13

well I'd guess you need to identify the following things
a monetary value (the amount paid)
a date (the date / time the payment was made)
and some method of linking the payment back to a customer and / or specific booking
and some method of identfying what type of payment it is

and you need to make certain you only allow one off any one type of payment

so do you think you need to associate a payment with:-
a specific customer?
a specific booking?
or both?

how do you know what a payment received is for?
how do you enforce that requirement that you can only have one payment per payment type on any one interaction with the customer?

based on the business requirement what makes anyone payment unique
does your model support that
how do you propsoe to enforce it. bear in mind some might argue you sould enforce that int he application layer. as we don't know the nature of your coursework that may be a perfectly valid answer

arayici · #11 (**permalink**) 04-25-11, 18:25

Quote:

Originally Posted by healdem

as we don't know the nature of your coursework that may be a perfectly valid answer

I don't think we need a link between clients and the payment as client has relationship with payments.

here is the part of my coursework about booking and the payment.

Quote:

Clients make bookings. Each booking is for one client. Faraway find that many clients make repeat bookings, so that a client may have many bookings, each for a different trip. Bookings are for trips. Each booking is for a single trip. There are generally many bookings for each trip but a trip may have no bookings. Each booking is identified by a unique reference code which starts with the letter 'B' followed by three digits. Data to be stored for each booking is the booking reference code, and the date when the booking was made.

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.

futurity · #12 (**permalink**) 04-25-11, 18:43

OK,

Pretend that you have a table with one column, which is the primary key:

Code:

create table table1 (
    column1 int,
    constraint table1_pk primary key (column1)
)

You seem to have figured this much out.

Now pretend that you have a similar table, but with two columns, both of which are in the primary key:

Code:

create table table2 (
    column1 int,
    column2 int,
    -- primary key constraint goes here
)

1) How would you create the primary key constraint for table2?

2) What have you accomplished by having multiple columns in the primary key?

Now, the fact that a column is included in the primary key is completely independent of the fact that a column is included in a foreign key to another table. Do not confuse these two concepts: a column can be either, both, or neither.

3) Without changing anything you've already done, make column1 of table2 a foreign key to table1.

If you can accomplish these 3 things and understand why you're doing them, then you should know how to set up the relationship between your payments and bookings tables. If not, then I strongly suggest that you spend some quality time with your teacher, teacher's assistants, and/or fellow students to help you with this.

arayici · #13 (**permalink**) 04-25-11, 19:10

Quote:

Originally Posted by futurity

How would you create the primary key constraint for table2?

Code:

create table table1 (
    column1 int,
    constraint table1_pk primary key (column1)
)

Code:

create table table2 (
    column1 int,
    column2 int,
    constraint table2_pk primary key (column1, column2))



alter table table2 add constraint table1_tab2_column1_fk foreign key (column1) 
reference table1 (column1) enable

I was thinking about this but i was not sure making the type PK.

so is it look ok like this then.

i can insert only one payment of each type per booking.

healdem · #14 (**permalink**) 04-26-11, 05:20

because you already have defined a realtionship between customer and booking you don't need both. as the payment is for a specific booking then I'd expect the booking reference to be included in a payments table, rather than customer or customer AND booking.

the thing you are missing is payment type.

if you make the primary key of the payments table a composite key of booking AND payment type then you can enforce the one payment per type per booking requirment. you could use an autogenerated ID to make a payment unique, but as you have a perfectly valid natural key Booking + payment type I don't see the point.

you also need another table which defines payment types

Internet Services

Web Development

Digital Marketing

Consumer Tech