Hello,
We have been proposed two database schema and are faced with
making a decision between them. I am fairly new to Postgresql and am
looking for advice on which would work best. Both would be done in
postgresql.
Our database will hold lots of different data types and will be queried
frequently and have new data inserted rarely (in the long run). So we
would like to have it be most efficient with queries. Only a few users
would have permission to add data, most will only be allowed to query
the database.
One schema has on the order of 100 tables and the different types of
data will be stored in different tables. Most tables will reference at
least one other table. The second schema has on the order of 10 tables
with more data stored in each and multiple types of data in a single
table. In this case tables will reference others, but also then
themselves in the case where different types of data is stored in a
single table.
The person designing the database schema has recommended we use
the simpler design with more data per table. My initial reaction was
that the schema with more tables would be faster to query. But I'm
now wondering if we could use indexes on the schema with few table
to search it quickly. Can anyone speculate as to which database
schema might be more efficient with queries. Our queries will most
often require searching through different types of data in a single
query.
Any advice would be appreciated.
Thanks so much,
Jennifer
