[RESOLVED] Database Schema Question

[Rodney_H_]

This is a question about database architecture:

I am in the process of writing an application for a network of non-profit organizations in the state. The gist of it, without going into all the features, is that it captures information into the database. All kinds of information into several tables.

If it is useful, other organizations will want to use it and form networks in theri states as well, as it allows them to pool resources and plan things together for a greater possible result.

The trouble is, I am trying to plan ahead for the time when we allow other states to form their network on the site, as we have only launched a beta version a couple days ago, but the response has been overwhelmingly positive.

So, here is my question:

Would it be better to put each state's network's info into their own database, or should I use one LARGE database, and just filter information by state when presenting the info to the visitors of the site?

Also, how best architecturally to deal with this on the site itslef? Should I make a directory for each participating state like:

http://www.site.org/mn

http://www.site.org/wi

http://www.site.org/il

And, depending on which directory they are in, I will know HOW to query the database and which information to present them... ??

I hope this is not too vague. I am just trying to, as I said, plan ahead.

Any thoughts or suggestions for things I should read would be appreciated.

THANKS!

[Sxooter]

Put it all in one table with reference to a master table of states.

create table state_master (id int autoinc primary key, statename text);
insert into state_master (statename) values ('Alabama');
...
insert into state_master (statename) values ('Washington');

create table state_info (sid int autoinc primary key, state_id int references state_master, fireld1 text....

[Rodney_H_]

Sxooter,

I think I understand. Having one table will be easier to manage/maintain, and the MASTER table can hold all the parameters/configurations that I need for each one.

Cool!

Thank you so much for your help.

-Rod

[Sxooter]

you're welcome. You'll also find that a proper relational setup will make it easier to update the information, and easier to write queries. For instance, you'll wind up with queries like:

select * from table1 a join table2 b on (a.id=b.a_id) where a.state_name in ('michigan','florida');

[Rodney_H_]

Rock and Roll!

Thanks, Sxooter.