The Beloved Community Relational DataBase
It is clear to me, from what you described, that you need all the
security, normalization and engine that an r.d. provides, but that you
dont need a full powered r.d. like Oracle or SQL Server 2005, because
your data volumes are small.
I would recommend either one of these r.d.:
- Microsoft Access
- Microsoft SQL Server Express
Microsoft Access is the simplest r.d. of the 2 and is well suitable for
data volumes up to a few dozen thousands records per table. It is not
recommended for databases with tables having hundreds of thousands of
records. Microsoft Access is not free, but is part of MS Office Pro. I
guess you can buy it separately: the price is certainly not high. It is
not as secure as Microsoft SQL Server and is a little bit more limited:
it does not allow to create Stored Procedures for example.
Microsoft SQL Server 2005 Express is the Express version of Microsoft SQL
Server 2005 huge enterprise r.d. system. It is totally free and yet
retains most of the power of SQL Server 2005. It does not come with
Enterprise Manager or Query Builder which are the SQL Server Management
and Query applications which lets you administer and query your databases
with a nice GUI. However you can also download the free SQL Server
Management Studio Express which offers you a similar, complete enough and
very nice Management application for your SQL Server Express databases.
As a matter of fact SQL Server Express even has a big advantage over SQL
Server in that an SQL Server Express database is made of only one file
with an .MDF extension, which you can move on your PC. This is not the
case of the more complex SQL Server databases. So it is very nice to be
able to move databases from one location to another one.
I would go for Microsoft SQL Server Express and SQL Server Management
Studio Express. I have been using them and they are nice and simple
enough to use as long as you understand what an r.d. is (i.e. tables,
rows, columns, primary keys, foreign keys, relationships, nullable
columns, → but these are the basic Relational Database notions which you
certainly already know of).
Here are the links for downloading these free tools:
Microsoft trial software
(be sure to look for the Download SQL Server 2005
Express Edition toward the bottom of the page: I gave you the link above
which also contains instructions to download other versions of SQL Server
because it contains a comparison of SQL Server Express 2005 and of SQL
Server 2005 and also the conditions upon which you may use SQL Server
download Microsoft SQL Server Management Studio Express
Then you can use either my TADO5 object (part of APL+Win Objects 6) from
APL+Win to do everything you want with you SQL Server Express database or
you can use Davins ADO workspace as well. Both are APL tools to exploit
your r.d. database: and both of these tools can work with mostly any
Relational Databases since they rely on ADO (Microsoft ActiveX Data
The doMapAddressDB and emClergy are clear candidates to become tables in a Relational Database.
Just a few remarks:
- it is now commonly accepted that the ID column in each table should be an auto-increment numeric field
- you can have your current ID field be renamed: Abr (for abreviation)
- the Abr column should be made Non Nullable and contain only Unique values in the Institution table
- the Abr column should contain only uppercase letters (it makes it otherwise difficult for users/developers to remember which letters are uppercased and which are lowercased)
- it is commonly accepted in rdbms to use camel case for naming tables and columns
- you should name your tables: Institution and Clergy
- you should rename your ID fields: InstitutionId and ClergyId (this is also a common practice to prefix the Id column with the table name)
- you should have more tables:
- - City for the various cities
- - State for the various states
- - Family for the various Christian denominations and Jewish movements.
- the INSTITUTION column in emClergy (i.e.in Clergy table) should be a pointer to the doMapAddressDB (i.e. the Institution table) INSTITUTION column, i.e. the INSTITUTION column in the Clergy table should be named InstitutionId and contain a numeric value which points to the corresponding line with the matching InstitutionId in the Institution table
- repeating the CITY and ZIP columns in the Clergy table is violating Normalization rules in that it duplicates data with the same columns in the Institution table (unless the CITY and ZIP are a personal property of the Contact and may be different from the Institution CITY and ZIP). The same remark could be maybe made of the Clergy PHONE column.
So, to sum up, I see your relational database as being defined as follows:
5 tables: Institution, Clergy, City, State, and Family.
- Institution table columns:
(note: it is better to call the INSTITUTION column Name since your SQL statements will include things like: Institution.Name which will be quite clear and better than Institution.Institution: its also common practice to have a field called Name in almost each table to represent the name of the entity)
- InstitutionId (auto-increment numeric values)
- Name (used to be: INSTITUTION)
- CityId (numeric value pointing to CityId in the City table)
- StateId (numeric value pointing to StateId in the State table)
- Clergy table columns:
- ClergyId (auto-increment numeric values)
- Name (used to be: CONTACT, maybe should be "Prefix, Fname, MI, Lname, Suffix")
- InstitutionId (numeric value pointing to InstitutionId in the Institution table)
- FamilyId (numeric value pointing to FamilyId in the Family table)
- Family table columns:
- FamilyId (auto-increment numeric values)
- City table columns:
- CityId (auto-increment numeric values)
- State table columns:
- StateId (auto-increment numeric values)
(note: for the last 3 tables you may want to have 2 fields called: Abr and Name instead of just Name. Abr would be the Abreviated name like FL for FLORIDA and Name would contain the full name like FLORIDA)
If you prepare your data like described above, they could be inserted with no problem in a real Relational Database like SQL Server 2005 Express, with no change.
- Should we get all our tables as error proof as possible before we start or are there tools
in SQL Server Express to help us eliminate redundancy and correct errors.
Should we have a Status field in the Institution and Person tables to mark those where the data is relatively solid ?
- Email & Telephone can be specific to the Institution or can specific to the Person.
Should we include those fields in both the Institution and Person tables ?
When SQL is producing a query result, can it easily use the person specific Email and if absent use the Institution info ?
- Do we need the both 'InstitutionId' & 'Abbr' ? Except for memorability, can't we use 'InstitutionId' just as well.
November 20, 2007.
- Eric, let's create RDB using Microsoft SQL Server Express & SQL Server Management Studio Express
- Carl will use the TADO5 Object (part of APL+Win Objects 6) to access or manipulate the RDB
- then we'll create general "RDB" syntax that will work with any Relational Database
- since TADO5 relies on ADO (Microsoft ActiveX Data Objects).
- Programmers who don't wish to use APL can use Microsoft SQL Server Express
- All of us will use SQL Server Management Studio Express to edit or display the RDB
- Eric, will the Beloved Community RDB be on the internet so people with access can edit & expand it
- or will I distribute copies for people to edit & expand and then I consolidate them ?
- below hopefully will be all the commands necessary to create the RDB using our 5 tables:
- bcInstitution bcPerson bcDenomination bcCity bcState'