Re: OT: MySQL versus PostgreSQL and database design
- From: Joe Pfeiffer <pfeiffer@xxxxxxxxxxx>
- Date: Thu, 30 Apr 2009 14:03:13 -0600
Christopher Tidy <cdt22NOSPAM@xxxxxxxxxxxxxx> writes:
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.
Ah, sorry, I misunderstood (whew!). I guess this would depend on how
different the event types are -- if they've all got exactly the same
fields, then just having an event type field may well be the most
logical approach. But if they're really *different* types of events (in
spite of having the same fields), then breaking them up may sense.
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?
No, that wouldn't be a worry (establishing a connection to the database
takes a while, but not grabbing a table). On the other hand, I wouldn't
expect filtering on an 'event type' field would be terribly time-consuming.
Sorry I'm being so wishy-washy on this one...
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.
Besides, if you're anything like me, you'd never get around to learning
it until there was a real project to learn on!
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.
Vastly more satisfactory. That's what databases are good at.
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.
Hopefully my wishy-washy answer above does a better job of explaining
what I was trying to get at on this one.
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.
Surprising to see an O'Reilly get bad reviews (though I haven't looked
at their DB books).
.
- Prev by Date: Re: long term reliablity computer boards
- Next by Date: Re: Memory Lane, slightly OT
- Previous by thread: Re: Methanol good for anything in the shop?
- Next by thread: Re: More Names 2009 photos
- Index(es):
Relevant Pages
|
