Re: OT: MySQL versus PostgreSQL and database design

Joe Pfeiffer wrote:

Hi Joe,

Thanks for the thoughts. Once again, sorry I've been slow to reply. I'm working on several projects at once.

What if you had an 'events' table, that had the parts of events that
were common to people and to products, and an ID? Then a
person-events table could have fields specific to a person, and the ID
of an event, and a product-events table could have fields specific to
a product, and the ID of an event.

I had wondered about this. But the thing is, I'm never going to want
to retrieve all the events at once. I'm only going to want to retrieve
all the events relating to a single person or product at once. So I
thought it might make sense to split the data into many tables rather
than few, so that each table contains exclusively the data I'm going
to need at one time, to avoid searching/sorting?


If I understand what you're suggesting, putting individual events (as
opposed to event types?) in different tables would be an odd way to do
things. You'd have to do something like establish DB connections based
on event ID (or similar) to retrieve the table for the event -- in
effect, you'd be doing the searching in your application instead of
taking advantage of the database. If you're able to define a key
in a way that you can avoid any real searching in your application, you
can make that a primary key in the database.

No, I don't think I've conveyed my meaning correctly. It would not be one event per table, but one type of event per table. I was thinking of sorting the events into different tables so that each table contains one type of event - specifically a subset of events that I'm likely to want to retrieve all at once, without any sorting.

Does establishing a connection to a particular table take a significant period of time, such that I would need to minimise the number of connections to tables that I'm going to open and close?

Figuring out how to organize these things is what databases are good
at. By simply figuring out what your most-common search keys are and
making them primary, and then not worrying about the problem any
further, you're leveraging a *huge* amount of work done by other
people. Unless what you've got in mind is really weird, you are
extremely unlikely to come up with anything more efficient than just
using the DB as intended. If what you're doing is that weird, you may
be better off using flat files and index tables than a DB in the first
place -- but that's really, really unlikely.

I'm sure you're right here. Ultimately, this may not be the simplest beginner's database project I could choose, but it certainly isn't the most complicated either.

I was guessing that using the database for searching and sorting as much as possible would be more satisfactory than using a PHP script for the same purpose.

However, events could be broken down further by month and by country,
etc. Taking this process to its ultimate conclusion could, at a quick
calculation, give me a database with 100,000 tables. This is why I'm
wondering if having a large number of tables can be a bad thing.


I'm a little vague on just what an 'event' is, but if every event
takes place in a particular place and with a particular time, then
you'd want to combine them into a single table.

Even if you didn't want to retrieve all the events at once?


Yes.

I'm afraid I don't quite understand why this is the case. Possibly my explanation has been too abstract. But if it adds anything, each event will have the same fields in a row, with no empty fields. But I will never want to retrieve all the events at once. I will always want to retrieve a subset of the events, and I was thinking of sorting them into tables according to subset, so that the database didn't have any sorting to do when it came to retrieve the events. Instead it would just need to sort the events when inserting them into the different tables.

You also mentioned O'Reilly books a while back. I already have Jonathan Gennick's "SQL Pocket Guide", a great little book which explains the differences between the major SQL database systems.

I had a look on Amazon and the only suitable O'Reilly PostgreSQL book I could find was this one:
http://www.amazon.com/PostgreSQL-Developers-Library-Korry-Douglas/dp/0672327562/ref=cm_cr_pr_sims_t/179-1842309-4406524

However, it seems to have a lot of bad reviews, saying it is lacking important information, especially in the index. Do you (or anyone else) have this book? If anyone does, it would be great to have another opinion on it.

Many thanks,

Chris

.

Relevant Pages

  • Re: OT: MySQL versus PostgreSQL and database design
    ... to retrieve all the events at once. ... can make that a primary key in the database. ... likely to want to retrieve all at once, without any sorting. ... different the event types are -- if they've all got exactly the same ...
    (rec.crafts.metalworking)
  • Re: OT: MySQL versus PostgreSQL and database design
    ... and avoiding an absolute "the design is broken" pronouncement. ... to retrieve all the events at once. ... in a way that you can avoid any real searching in your application, ... can make that a primary key in the database. ...
    (rec.crafts.metalworking)
  • Strange slow datagrid problem - any suggestions dotnet 1.1
    ... database and displayed them in a datagrid for sorting and searching. ...
    (microsoft.public.dotnet.languages.vb.controls)
  • Re: Help - Timing Logic
    ... from the database ... ... now you either need to incorporate a locking procedure ... ... opposed to building the transaction in the code. ... implement as above locking only the records you retrieve / update - need ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Getting a (non-radio) buttons index number from array
    ... index in the Comment array of the button that was fired. ... you don't need js if you print out the full DOM ... There needs to be some unique way to identify the row in the database. ... Then it's easy to retrieve the ...
    (comp.lang.php)