Database Servers

The goal

• Provide schema create statements, views, and procedures for all significant Database Servers.
• Provide sample access code in all major programming languages.

During the Alpha stage, the focus will be on PostgreSQL and Mono C#.

Other database servers that will be supported in the future include NuoDB, DB2, and MySQL. Future language support includes Go, C++, Python, and PHP. Donations are gladly accepted. Your contributions will get things moving faster :smile:.

The SQLFairy project provides the translations from the XML schema specification to the target SQL Server.

Views and especially procedures will require some custom work. The idea is to keep the custom work to a minimum. DBFit Unit Testing will be used to help ensure the server specific views and procedures work across all supported database servers.

People and legal entities

People

• Change their names over time
• Are born and have birthdays
• Do eventually die

1. These change events happen at specific times and places.
2. Just having a first, middle, and last name field may not be good enough.
3. Name changes made without losing history.
4. Names are actually complicated - a standard way to store and compose them without complicating searches.
5. If a table needs a field to link to an actual person, then it is possible that that same field needs to link to legal entities also.

To solve these issues

• People and legal entities are combined into a single table.
• Actual names are stored in another table to facilitate searches.
• That table is transaction-based, which means that the individual person or entities ID is not unique in the table.
• Every change to an individual person or entity is recorded as a new transaction in the Individual table.

Person and Entity Examples

Lists

Individuals

• Get put on lists
• Get taken off of specific lists
• Need to be removed from all lists, even new ones they are added to after global removal

1. These events happen over time
2. Individuals may need to stay on lists as inactive members
3. Global removal from all lists should not affect individual list membership or active status. For example, an individual may be on several lists, some as inactive members. When this individual is made inactive for all lists and then is later activated again, those previous membership states need to be preserved.

To solve these issues

• A transaction style subscribe and un-subscribe table
• Subscription to a NULL list name, un-subscribes the individual from all lists
• Only the latest subscription transaction is considered for each list

List Examples

E-mails, URLs, Address, etc.

E-mails, URLs, Address, and other Individual attributes.

1. These values change over time.
2. Individuals may have none or many of any particular type.
3. Individuals may share these attributes with others.

These and other individual attributes are stored in their own tables and linked to individuals using a transaction based linking table. There is no limit to the number of attributes any individual can have. Each attribute state change is saved for history.