db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Craven <jcra...@mail.freeshell.org>
Subject Re: General DB Question
Date Wed, 24 Nov 2004 16:51:25 GMT
Google for "database normalization 3NF" for more on this, but in a
paragraph the universally employed theory is that you should have a
table for each entity and relationship, and no redundant or non-atomic
columns anywhere.  So separate tables, as specific as possible.
Otherwise the data will be too inflexible, and can easily become
corrupted (changing something in one place, forgetting to change it in
another, etc.).  It's the RDBMS's job to sort through it all
efficiently and give you back what you wanted, or what you will want
two years from now that you didn't know you'd ever want when you
originally stored the data.  The book "Java Database Best Practices"
from O'Reilly has a good overview of this in just a few pages; another
book "Database Administration" by IIRC Chris Mullins goes over it in a
lot more detail, as well as the process of denormalisation for
performance if necessary.  (Still I expect you'll find that Derby can
handle tens of thousands of records easily.)  But there are probably
plenty of good explanations of these topics on the web as well.  There
are also plenty of threads on comp.databases where all the FAQs are
answered.

Also the site <http://www.databaseanswers.org/data_models/index.htm>
has a bunch of sample normalised data models, one of which might be
close to what you're looking for.

HTH

Jon Craven

acemccloudxx@comcast.net writes:

> I'm using Derby in an open-source project and I'm debating this
> question with myself. As I haven't come up with a good answer, I
> thought that I would seek some help.
>
> The program in question is extensible in that various kinds of data
> (that could be related to other kinds) will be stored in it, possibly
> even types that we haven't imagined yet.
>
> So the question is, would it be better to keep the number of different
> tables small and use the same tables for all the data or would it be
> better to use separate tables for each type of data.
>
> In the one case, it would might be easier to find all entries that
> relate to a given subject (even if they aren't the same kind of data),
> but key files would (of necessity) get large. In the other case, key
> files should be more manageable, but multiple queries would be
> required to find all the data records that relate to a single subject.
>
> I expect that DB sizes could easily hit tens of thousands of records.
>
> I'm not that experienced with database management issues, so I find
> myself at a loss on picking an approach that would be best overall.
>
> Ace


Mime
View raw message