Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2011
    Posts
    40

    Table needs check!!

    Hi Everyone,

    I created a table for the scenario below.

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by arayici View Post
    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 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by r937 View Post
    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??

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

    INSERT INTO payments
    (booking_code, type, amount)
    VALUES ('B001', 'Insurance', 200);
    ORA-00001: unique constraint (ORACLE_SQL.PMT_BOOKING_CODE_PK) violated
    so how i will insert multiple payments for the same booking?
    Last edited by arayici; 04-25-11 at 03:43.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by arayici View Post
    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
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by healdem View Post
    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?

  6. #6
    Join Date
    Apr 2011
    Posts
    40
    any idea anyone.
    I am stuck here.....

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by arayici View Post
    So is that my mistake to use the booking_code as a PK?
    Yes.

    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.

  9. #9
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by healdem View Post
    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?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by healdem View Post
    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.

    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.

  12. #12
    Join Date
    May 2008
    Posts
    277
    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.
    Last edited by futurity; 04-25-11 at 19:55.

  13. #13
    Join Date
    Apr 2011
    Posts
    40
    Quote Originally Posted by futurity View Post
    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.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •