Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008

    Report friendly database design

    Hello everyone. I'm a relative novice when it comes to databases and am having trouble designing a particular part of a database which I'll now describe.
    Basically I'm storing information on equipment which is hired out. It comes in three broad categories:

    Personnel ( Electricians, plumbers etc )
    Plant ( Things like generators, cement mixers etc )

    The system using the database needs to be able to access any item with a unique id so I've also created three tables and a crude sort of lookup table.

    personnel ( id*, created, modified, description, cost_per_hour, ... )
    plant ( id*, created, modified, description, on_hire, date_of_purchase, ... )
    vehicle ( id*, created, modified, description, on_hire, date_of_purchase, ... )
    lookup ( id, type )

    The tables all share a common sequence for id generation

    To find what I need I initially do a lookup on this lookup table and then query the relevant table based on the type. This setup was working ok until I needed to create reports and it seems it's grossly inadequate for that. At present the only workable solution I can think of is to combine them all into one big table which just doesn't seem right to me.

    Basically then I'm hoping someone with more experience than me can suggest a better design. I'm reading up on databases as fast as I can but I'm under pressure to get this working so any help would be very much appreciated!

    If I've left anything out please just ask and I'll get back asap.

    Thanks in advance...

  2. #2
    Join Date
    Jun 2003
    Sounds like you are using Oracle? Your solution may be code-specific, so you might want to post this question in the Oracle forum.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jan 2008
    Thanks for your reply. I'm using Postgresql for the database. I don't think the design issue is database specific to be honest. There have been developments since I posted however and it seems the design I initially outlined is no longer valid so feel free to lock / delete this thread. Once again thanks for your response

  4. #4
    Join Date
    Jun 2004
    Arizona, USA
    PostgreSQL supports an object oriented feature called table inheritance, which may 'fit the bill' for your situation.

    You could define a single, common table, containing all the common fields for your three tables, as well as the 'type' identifier. (I would also add an object_type lookup table, with a TypeID and a TypeDescription field.)

    After the first, base table is defined, add the the three specific table, each containing only the fields unique to the object in question.

    Ref this thread for a description of table inheritance, as well as the postgresql documentation for details of table inheritance.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

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