Handling many one-to-many relationships
Posted by: Simon F
Date: December 10, 2007 04:55PM

Hi all, I am designing a database where a table has 4 (maybe more in the future) one-to-many relationships with other 4 different tables.
It looks quite hard for me to fetch these data and build an object in my application (PHP) using a join.
The simplest thing would be to fetch the object from the first table and then execute 4 different queries on the 4 tables to assign the results as lists (arrays) to the object.
I'm sure that this is not the best solution. Keep also in mind that I would need to display 20 objects per page: this would be 1 query to fetch the objects plus 20*4 queries for the relationships; 81 sql queries for a page doesn't sound right to me.
A second solution would be to use one large table to store the whole amount of data for each object; but in this case the number of the columns would be very high (about 100) and selecting them without using "*" (which seems very harmful in a production environment) would be almost impossible.
Do you have any suggestion? Any best practice? Thank you in advance for your time.

Options: ReplyQuote


Subject
Written By
Posted
Handling many one-to-many relationships
December 10, 2007 04:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.