SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database design problem

    this one has had my goat for a few days and i've avoided it for long enough!

    2 tables:

    bookings (id [primary auto increment], name, description, staff_id, client_id, is_paid [enum(0,1)]

    invoices (client_id, amount, is_paid[enum(0,1), created_date, paid_date)

    So in a perfect world, an invoice would be generated for each booking, and everything would be easy. BUT it doesn't make sense to only create one invoice per booking at a time, and each invoice will have a custom charge rather than fixed rates.

    What I need is to have one invoice cover multiple bookings. I have thought of a few ways:
    1. having a description on the invoice that details which bookings
    2. having a comma seperated list in the invoice table, listing the invoice IDs

    2 is probably the best for data connectiveness (is that even a word?), but I still get the feeling that it doesn't seem like the best way to do it.

    Can anyone suggest a better solution?
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  2. #2
    SitePoint Wizard jimbo_dk's Avatar
    Join Date
    May 2005
    Location
    Singapore
    Posts
    1,261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The way I do it is to have a third table:

    invoices(invoice_id(Primay Key), is_paid, created_date, paid_date)
    invoice_items(invoice_id(Foreign Key from Invoices Tables), item_ID, booking_id(Foreign Key From Bookings), amount)

    Hope that helps...
    Winners Respond. Losers React.
    Singapore Web Designer

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    2 is never the best way to do anything

    you would definitely want a 3rd table, with one row per booking per invoice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Feb 2005
    Location
    Brisbane, Australia
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Modify the bookings table. Add a field to indicate which invoice number a booking belongs to. Then, to view all the bookings for invoice 0001, you would query:
    PHP Code:
    mysql_query 'select * from bookings where invoice_id = "0001";' ); 

  5. #5
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah yes, all these suggestions make way more sense! I knew there was a database design level I hadn't quite aspired to... in simple terms I just need an association table that says booking_id x belongs to invoice_id y ... easy!
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development


Bookmarks

Posting Permissions

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