This is a draft of a framework database design to manage a system where the user can define and manage tables and fields, their corresponding HTML form objects for gathering input to them, their summaries, and the relationships of the tables to each other. In some sense its a foundational ERM, but stored in the database itself rather than being consigned to an external configuration file.
Think PHPmyAdmin, except the table relationships are stored and the user has much more control over the form appearance for the inputs.
For, at it’s heart, aren’t all websites little more than forms and reports before any of the bells and whistles get added?
The framework in question is PHP, the database engine will be MySQL in the first version, but I plan to write drivers to support MSSQL, Postgre and possibly Oracle. I’ve chosen to place this thread in this forum because it is dealing purely with the database structure concerns of the setup.
Unless otherwise noted, all tables have an id field for their primary key.
sqlentities
This first table is a “collection” table. It’s primary key is auto-indexed (or the equivalent for the underlying engine). All the tables we will be discussing are members of the sqlentities collection.
A collection is a group of tables whose keys are unique across tables. All keys of a collection ultimately come from this collection table. So if record #3 is on the tables table, there will be no other table in the collection with record #3 except the collection table itself.
This approach allows for joins between the tables of the collection that would otherwise be ambiguous.
Fields
[list][]tables_id The table within this collection that holds the record.
[]sort Arbitrary sort (for when the user wants to drag and drop items into an order that has no basis the computer could otherwise derive, such as alphabetical or numerical).
[/list]
collections
The second table is the table that tracks the collection tables. At install time there is one entry here, for sqlentities. Users can define other collections. NOTE - tables do not have to belong to the same collection to reference each other using foreign keys.
Fields
[list][*]prefix All tables of a collection have a prefix, which is a short string followed by double underscore. The prefix is stored here and, for the moment, is the main reason for this table existing. NOTE - the system collection sqlentities must also be defined in the configuration file. It is defined there with the default of “gz__” meaning the true name of all the tables here are “gz__collections”, “gz__tables” and so on. Without the definition in the configuration the system wouldn’t know where to find the collection map. While this breaks DRY principle, that break is preferable to having the system tables behave more differently than the user defined tables than they need to.
[/list]
tables
The tables of the database. This includes the very tables we are talking about right now! This is a reflexive, eat your own dogfood approach in my mind. If the code that manages the user tables can’t manage it’s own tables, what good is it?
Fields
[list][]nameThe database’s name for the table. While it would be unusual for the user to want to change this (and hence I considered letting this be the key for the table) the situation might come up so numerical keys are used. This field has the constraint that it must be unique for the collection and the database.
[]classThe PHP class that manages the table’s behavior. The framework defaults to the table class when this field is left null. The possible entries here are drawn from the file system and presented as a dropdown. This way the programmer can create the new class, place it in the file system and use it immediately without having to monkey with configurations. For behavior consistency the programmer must extend from the Table class when creating a custom table handle class.
[/list]
tables_fields
This is a cross-reference table and has it’s own PHP class to handle tables of it’s type. Cross reference tables in general manage many to many relationships between two or (rarely) more tables. They may or may not also have fields which define additional information specific to the relationship between the tables.
This cross reference table maps tables to fields. In this system field can occur on more than one table, but unlike a freeform SQL system if a field has the same name on more than one table it’s role and purpose are likewise the same. This means its validation, form presentation, options and all other attributes will be the same for all tables the field occurs on. The data the field stores from will be different, and depending on how it’s managed the field might draw upon different data as well.
At the PHP input level users are restricted from giving a field a name ending in “_id” This suffix is reserved for foreign keys. When a new table is created a foreign key field record for the table is likewise created and reserved for it whether that table eventually is joined to another table or not. Due to this stricture, all references to a table throughout the system will have the same name.
(The SQL gods made aliases for those occasions when the same table must be joined twice to another).
Fields
[list][]tables_id Foreign Key to tables
[]fields_id Foreign Key to fields
[/list]
fields
The fields of the database. Fields are allowed to be on more than one table, but be cautious about doing this since if you put a field on more than one table collating can be difficult - and often a foreign table is appropriate. That said some fields, like createdby, modifiedby, and so on will appear on multiple tables since they establish record change times or information that isn’t useful for collating (the arbritrary sort field is a great example of this). Also, foreign key fields will obviously appear on the multiple tables that reference them.
Fields
[list][]name Field’s database name. If the suffix is “_id” then the field is presumed to be a foreign key.
[]maximum Maximum value of the field
[]minimum Minimum value of the field
[]default Default value of the field
[*]flags Flags of the field (see table flags)
[/list]
flags
Sometimes a field is overkill, let alone joining another table. Flag fields hold collections of these yes/no statuses as a bitfield using the BIT field type. This table holds the meaning of the flags.
Fields
[list][]fields_id The field that is a flag collection.
[]bitvalue Tiny Integer - Exponent of the bit this row describes
[]title The displayed title of the flag.
[]description The description of the flag.
[/list]
options
Some fields are selects from a set list of options. MySQL has ENUM for this, but Gazelle will support this more abstractly for data portability between engines.
Fields
[list][]fields_id Foreign key again to the field in question.
[]label -Text – label of the option in the view drop down.
[*]value - Text – value of the option in the view drop down.
[/list]
forms
Here we begin to move from the internal world of the database to its external presentation. A form to Gazelle is the presentation of a single record for editing. It usually involves only one table, but multiple tables can be involved and the engine supports creating forms that have nested entries to corresponding child tables.
Tables are uncoupled from their forms and forms are uncoupled from their pages since different pages might want to present the same form. Forms are still considered SQL Entities since they comprise the gateway to the database for the outside world. Most tables have one form, but not all.
Fields
[list][]tables_id Foreign Key to the primary table of the form. Just as tables can act as fields, forms can act as inputs to another form allowing hierarchal input to be performed.
[]title Visible title and name of the form
[]description Description of the form
[]class PHP class governing the form’s behavior (Null selects the default).
[]flags Form flags.
[]template View Template used for the form (Null selects the default)
[/list]
fieldsets
Fieldsets form groupings of form information in a logical manner, especially for a form that is presenting a table with multiple foreign tables. A form is not required to have fieldsets, but it is encouraged.
Fields
- legend - Legend text
- description - Fieldset description
- template -View template
inputs
The inputs of a form, including other forms for tables that gather information for multiple tables.
Fields
- forms_id Foreign key to the form of the table.
- fieldsets_id Foreign key to the fieldset (null if not in a fieldset).
- label Label of the input
- description Description of the input.
- type PHP Class that governs the input type, null defaults to the class for text fields.
- flags Flags of the field.
- validation PHP Class that governs the input validation.
- row Row of the input within the fieldset. Inputs without fieldsets appear before those in fieldsets.
- column Column of fieldset
- template View Template, null to default for type.
summaries
The user can define a basic tabular summary for a table and that information is here. That said nothing here demands the output go into any specific style - you can use template and php class definitions to refine the summary to your needs. In a sense summaries are view - but I hesitate to use that term since in the MVC paradigm the term view talks about the code that preps the user’s view, which is outside the database code.
In one way or another summaries are the backbone of many websites. A page might show one or more summaries. These aren’t meant to be individual record displays though - those are usually too specialized
Fields
[list][]title Summary title
[]caption Summary caption
[]tables_id Primary table of the summary. Other tables may be used, but they must have a foreign key on the primary table (or vice versa). This is the table in the FROM clause.
[]maxrows Maximum rows of the summary
[]orderby Default field to order on
[]flags Summary flags
[]class PHP class to assemble summary. Null for default.
[]template Template of the summary. Null for default.
[/list]
column
Columns correspond to fields on the primary table or on adjoining table. A table adjoins the primary table if a foreign key appears on the primary table for it, or it has a foreign key to the primary table. Summaries that require passage through a cross reference table will be beyond the scope of the base system (which is why you can specify the handling PHP class for summaries for when these cases arise).
Fields
[list][]summaries_id Foreign Key to the summary.
[]tables_id Foreign Key to the table for the record.
[]fields_id Foreign Key of the field being displayed.
[]header Column header
[]order Column number from left.
[]template Template of the column
[/list]
That’s all for now. Apologies for the length of it.