The basis of automation is consistency. One needs to follow a consistent, replicatable pattern to avoid hand-coding CRUD pages for each table.
– Map out the tables and their relationships
– Follow a few simple rules in naming conventions
– Use table and column comments to pass on some additional info
Techniques I use:
– Table names can be different from row prefixes, but must be consistent for all columns within that table (e.g., events as the table name, event_ as the prefix for each column).
– Each table must have an auto-increment primary key, identified by the suffix _id (e.g., event_id would be a primary auto-increment field, typically the first column)
– Each field name must be unique within the schema (e.g., all my tables have an owner id, because multiple users are in the same database event_owner_id would only exist in the events table)
– If a table is going to be used as a lookup table for a field in a different table, it must have a field with a suffix of _name (e,g, the owners table must have a field called owner_name)
– Foreign keys must be created for each referential lookup (e.g., the event table has a lookup to the owners table, so a foreign key relationship must be created)
– Unique fields must have unique keys created
– Any table you don’t want users to edit, alter the table comment to hidden so it will be excluded from owner edit (e.g., the owners table is for use by the administrator alone)
– Any column you don’t want users to enter, alter the column comment to hidden in the schema (e.g., the _owner suffix column in any table)
– Use the column comment to add in any additional attributes you may want to use (e.g., for the event_dates table has an event_date_name field, assign its comment as datepicker to fire up a datepicker jQuery UI widget)
– Create a special table (I call it myschema) creating a relationship table that us used by the engine to orchestrate all information. I do this by running a stored procedure that creates the table from a query and assigns it a hidden table comment so it won’t be visible to users.