Thread: One big database or little separate ones?
In creating a database I am trying to determine which method is the better way to go. No, I do not know what "better way" means, novice is the key, hence this post. The basic question is, what point(s) are used to determine there are enough tables and another database should be created. Hmm, well let me say it this way. Here are briefly (for the most part) the things I want in a database; 1. customers - the usual stuff, names, addresses, etc. 2. well logs - depth of water wells, location (latitude/longitude as well as the normal address), layers drilled through, etc. 3. excavator work - hourly rate, length of dig, etc. 3. plumbing - items used on project, part cost, etc 4. septic work - installation of septic tanks, address, cost, etc 5. water softeners - customer name, is it a rental or not 6. supplies/parts - inventory type stuff. The basic decision here is, do I put all this into one database with a bunch of table? Or would it make more sense to separate it out? If there are separate databases will it prevent linking tables between the two? -- Registered Linux user 193414 http://counter.li.org "Trying"? My contribution was much closer to a "feeble wave in the general direction of something that might lead you one step closer to a solution if you squint really hard and do all of the work."
Dennis Veatch wrote: >On Saturday 10 January 2004 04:38 pm, you wrote: > > >>Hi, >> >>In my opinion, if there are relations between each parts, do only one >>database (only one connection needed). In the other case do a database >>for each separate part (like this no risk of bad manipulation). >> >>Bruno >> >> > >Yes there will be relations with nearly all if not all of the parts. > >One thing I forgot to mention, this will be plugged into postgis. At the >minimum I envision customers and well logs being used by postgis or vise >versa. > > > > >>Dennis Veatch wrote: >> >> >>>In creating a database I am trying to determine which method is the better >>>way to go. No, I do not know what "better way" means, novice is the key, >>>hence this post. >>> >>>The basic question is, what point(s) are used to determine there are >>>enough tables and another database should be created. Hmm, well let me >>>say it this way. >>> >>>Here are briefly (for the most part) the things I want in a database; >>> >>>1. customers - the usual stuff, names, addresses, etc. >>>2. well logs - depth of water wells, location (latitude/longitude as well >>>as the normal address), layers drilled through, etc. >>>3. excavator work - hourly rate, length of dig, etc. >>>3. plumbing - items used on project, part cost, etc >>>4. septic work - installation of septic tanks, address, cost, etc >>>5. water softeners - customer name, is it a rental or not >>>6. supplies/parts - inventory type stuff. >>> >>>The basic decision here is, do I put all this into one database with a >>>bunch of table? Or would it make more sense to separate it out? If there >>>are separate databases will it prevent linking tables between the two? >>> >>> > > > -- Bruno LEVEQUE System Engineer SARL NET6D bruno.leveque@net6d.com http://www.net6d.com
Hi, The only reason to separate databases, is if the data has no relation from one DB to another. Otherwise, you are just making things hard on yourself. Eric On Sat, 2004-01-10 at 14:14, Bruno LEVEQUE wrote: > > > Dennis Veatch wrote: > > >On Saturday 10 January 2004 04:38 pm, you wrote: > > > > > >>Hi, > >> > >>In my opinion, if there are relations between each parts, do only one > >>database (only one connection needed). In the other case do a database > >>for each separate part (like this no risk of bad manipulation). > >> > >>Bruno > >> > >> > > > >Yes there will be relations with nearly all if not all of the parts. > > > >One thing I forgot to mention, this will be plugged into postgis. At the > >minimum I envision customers and well logs being used by postgis or vise > >versa. > > > > > > > > > >>Dennis Veatch wrote: > >> > >> > >>>In creating a database I am trying to determine which method is the better > >>>way to go. No, I do not know what "better way" means, novice is the key, > >>>hence this post. > >>> > >>>The basic question is, what point(s) are used to determine there are > >>>enough tables and another database should be created. Hmm, well let me > >>>say it this way. > >>> > >>>Here are briefly (for the most part) the things I want in a database; > >>> > >>>1. customers - the usual stuff, names, addresses, etc. > >>>2. well logs - depth of water wells, location (latitude/longitude as well > >>>as the normal address), layers drilled through, etc. > >>>3. excavator work - hourly rate, length of dig, etc. > >>>3. plumbing - items used on project, part cost, etc > >>>4. septic work - installation of septic tanks, address, cost, etc > >>>5. water softeners - customer name, is it a rental or not > >>>6. supplies/parts - inventory type stuff. > >>> > >>>The basic decision here is, do I put all this into one database with a > >>>bunch of table? Or would it make more sense to separate it out? If there > >>>are separate databases will it prevent linking tables between the two? > >>> > >>> > > > > > > > > -- > Bruno LEVEQUE > System Engineer > SARL NET6D > bruno.leveque@net6d.com > http://www.net6d.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Sat, 10 Jan 2004, Dennis Veatch wrote: > In creating a database I am trying to determine which method is the better way > to go. No, I do not know what "better way" means, novice is the key, hence > this post. > > The basic question is, what point(s) are used to determine there are enough > tables and another database should be created. Hmm, well let me say it this > way. > > Here are briefly (for the most part) the things I want in a database; > > 1. customers - the usual stuff, names, addresses, etc. > 2. well logs - depth of water wells, location (latitude/longitude as well as > the normal address), layers drilled through, etc. > 3. excavator work - hourly rate, length of dig, etc. > 3. plumbing - items used on project, part cost, etc > 4. septic work - installation of septic tanks, address, cost, etc > 5. water softeners - customer name, is it a rental or not > 6. supplies/parts - inventory type stuff. > > The basic decision here is, do I put all this into one database with a bunch > of table? Or would it make more sense to separate it out? If there are > separate databases will it prevent linking tables between the two? Can a customer move, and request a new well be dug? Can someone move into a place where you put in a well before, and ask you to do something to the existing well? Does a well ever cave in, or need new excavating work? Do you ever get called in to change plumbing on a well? Same sort of questions vis a vis septic systems and water softeners. If these sort of questions need a yes answer, then you might want to consider having other tables. Gord
Hi all, This is a great topic for me, as I'm just now starting to consider this very issue. Is there any advantage to maintaining separate databases for backup purposes? Specifically, I am going to have a large number of tables with text info, and just a few tables that saves several gigabytes of images and data files. I was thinking of having the text data in one database to allow for frequent backups (every few hours), with the large image and file database backed up less frequently (once a day or less frequently). Is the balance in backing up several hundred megs of text data frequently v. several gigabytes of less critical binary data a good reason to maintain separate databases? All thoughts welcome... -Allan At 3:11 PM -0800 1/10/04, Eric Frazier wrote: >Hi, > >The only reason to separate databases, is if the data has no relation >from one DB to another. Otherwise, you are just making things hard on >yourself. > >Eric
Hi Allan,
> Is there any advantage to maintaining separate databases for backup
> purposes?
> Specifically, I am going to have a large number of tables with text
> info, and just a few tables that saves several gigabytes of images and
> data files. I was thinking of having the text data in one database to
> allow for frequent backups (every few hours), with the large image and
> file database backed up less frequently (once a day or less frequently).
If you do this you'll lose the benefits of all the pretty relational
integrity stuff when pic 17 in table A in database DB1 belongs to record
10 in table B in DB2. You can't connect database DB1 with DB2 in PG.
If you can afford this, why store the picutes in a database at all ?
Put all the images in a filesystem probaply on a RAID mirrord drive or
rsync or whatever.
Store the file paths in the database and you'll have fast file access to
the pics and not too much weight in the database.
If you insist in putting all in an RDBMS then you could use PG's schemes.
Use 1 big DB and some schemes within like
a) Schema "data"
b) Schema "images"
That's like having 2 sub-databases that can actually see each other e.g.
for having foreign key constraints.
Then use pg_dump to backup the database in parts as you like.
Look up the documentation of pg_dump:
--schema=schema
--table=table