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