I have attached datamodel.jpg from the schema by Barry Williams, Organizations and People. I have modified it in Paint Shop Pro to illustrate my question. I am only beginning in the realm of database design and need help with a database for a Twelve-Step organisation similar to Alcoholics Anonymous. This database will house both public and internal contact information for intergroups, which maintain twelve-step meeting lists for the areas they represent. There are also what we call lone groups, which are not members of any intergroup, and these have information specific to them such as meeting location, date and time, some of which will also need to be hidden/internal at the meeting's request. My questions are:

We need to distinguish intergroups from lone groups and I'm thinking the "Organisation_Types" table will achieve this. Should there be one table for all information relating to intergroups and groups, even though some information for one will not be applicable to the other, or would two tables be more logical? And how would 2 tables change the model? These are probably very basic questions, but complex to me, sorry.

We need to categorize intergroups and lone meetings by state/province, and country. Intergroups can cover one or more states/provinces, and 1 country, but lone meetings reside in only 1 state/province/country. How do I create a relationship that does this, or will the Addresses table take care of it by itself? Perhaps further relationships should be added to the "Organisation_Addresses" table? We want to list all US states, even if there is no data, and for those, display a message that currently we have no information for this state/province, etc... My thoughts are written on the schema image.

As I said above, some Intergroup/Group information needs to be hidden, some public, and some will be both. We will have 2 website areas, one for public and one for administration. How do I solve the hidden vs. not hidden issue, which will be field by field and record by record? Should I create 2 separate fields for everything? One for public and one for private? Seems like there has to be an easier way.

In a similiar issue, the People table will need to have some records public, some private, and some both. Should all these go into one table or two separate?

Then there is the question of searchability. I've seen some DB's where you can search by zip code, get maps, and all sorts of other fancy functions. This all seems WAY out of my league, but I'm hoping it's simpler than it sounds.

I've gotten this far, but now I'm stuck. I hope you can help and I hope I've been clear enough.

Thank you,
Beth S.

Not sure if it's a good idea to just copy a database and then try to fit your organisation into it.

Normally hierarchical groups would just fit into a table with a link to another group as it's parent. Your head group for the country (or the world) wouldn't have a parent and perhaps your lone groups might not either or perhaps they would link to the head group directly. You could simply have a flag to indicate lone groups.

You could also have a simple location hierarchy table that could contain the name of an area, the type of area and the parent area that it belongs to. I'm guessing the name of counties etc. This could be used to any level of location and for any country. Each entry in the group table could link to any record in the location table so "Orange County AA" might have a location id of 3 in this example.

Code:

id 	name		type		parent_id
1	USA		country		NULL
2	California	state		1
3	Orange County	province	2

Rather than using searching you could just show users a list of states, they'd click their state and then be shown a list of provinces etc. When you have groups that meet in the current location then display the public information about that group allowing them to contact them.

Meetings could also link to the location table but would need a complete address. To do the map you could just store a link to google maps for each group and allow users to click on the link on the web page - simple.

My list of main tables would be:
I wasn't sure whether you'd want the names of "clients" for groups in the database so I left that table off.

For public and private information you could simply have 2 queries that run depending on who is using the program (I assume it's web based). If the user is the general public then just select the fields that anyone is allowed to see. If they have the correct password then you run the select that shows them "all" the data for their group - or what ever they're allowed to see. If your requirements are more complex then we'd need something more complex.

I'm not sure how the "12 steps" come into the database design.

Just my 2c.

You haven't told us what database server you're going to be using, but, most database servers support the concept of views. A view is a window into your data, which essentially uses a query as the underlying 'filter.'

The interesting thing about views is that access to a given view is controlled by the role a user is assigned. You can assign specific users to predefined roles, which in turn 'controls' their view of the data. No users would be given direct access to the underlying tables. (Stored procedures would be used to insert/edit the data into the underlying tables.)

This approach lets the database handle the data security, in a manner that can't be easily circumvented. Even if someone hacked the web server (assuming you're using a web access to the data,) they still wouldn't have access to data that they're not allowed to view. In fact, if they didn't have a user's log-on credentials, they wouldn't have access to any data.

However, if access to underlying tables was granted to users via ad-hoc queries, once a web server was hacked, the hacker has access to all the data, just by removing a condition in the where clause of a query...

Use multiple layers of security in an environment where data must be kept confidential.