Data Modeling: See how Scott simplifies database modeling.

SQLsite, LLC - Web Database Design - Microsoft Access, SQL Server & Active Server Pages consulting

Data Modeling
See how Scott simplifies large databases with more than 500 tables.

Scott Reber has been developing multiuser relational database applications since 1987. In the course of his work, he has developed a data modeling technique. By categorizing tables by function and relating them with naming conventions, Scott has invented a new form of data modeling called Functional Data Modeling™ (FDM). FDM is a self-documenting relational database

design methodology based on the following premise: The function of every table can be categorized into one of 16 table functions* listed below. Primary key and foreign key column names adhere to naming conventions and it is best if the primary key is the first column(s) in the table. The rules are simple and the benefits are amazing.

When creating every table follow these 4 easy steps:

Pick a Table Function below that most closely describes your table. ex: 'Master'
Append your table name to the 3-character Prefix that corresponds with the function you chose. ex: 'masCustomer' would refer to your master customer table.
Append ID to that name for your primary key column name. ex: 'masCustomerID' would be the name of the first column in your master customer table.
Make sure all foreign key column names match the name of the primary key column they refer to. ex: the 'masCustomerID' column in your invoice transaction table (trxInvoice) table would reference the 'masCustomer' table whose primary key column name would be 'masCustomerID'.

Additional naming conventions:

Never use spaces in any names.
Never use underscores in table names except when differentiating between multiple foreign key fields that reference the same table. ex: A hospital patient has more than one physician, therefore your masPatient table may contain fields called Family_masPhysicianID and Referring_masPhysicianID.
Never use the ID suffix on the names of fields that are not restricted to a fixed set of values (domain) contained in another table. ex: TaxID
Never use plural names. ex: use 'masCustomer' instead of 'masCustomers'.
Never incorporate people, place, or company names into any table or field names.

Table Functions

No Table Function Prefix Function Description Examples

1 System sys A predefined set of tables found in every database that contain information about SQL Server. sysObjects, sysColumns, sysProtects

2 Application app Tables that provide application services. Configuration parameters, table driven menu systems, knowledge bases, user and group authentication, next number, project data. appConfig, appMenu, appUser, appSecurity, appNextNumb, appProject

3 Master mas The most common tables, typically have a single column primary key with linked detail tables. Master records are frequently updated. Reports are commonly based on master tables. Also used to resolve many-to-many relations between master tables. masCustomer, masProduct, masVendor, masTitleAuthor

4 Validation val Validation tables generally have two columns: the primary key and a description. Generally used to provide domain integrity via foreign key reference. Also provide longer key descriptions during reporting. Also provide excellent documentation. valState, valType, valPayCode, valCategory

5 Data entry dat Tables used for data entry. Often copied to local temporary tables for user input. A posting process would update Master tables and add new records to Transaction tables and delete the Data Entry table. datInvoice, datOrder

6 Transaction trx Generally sequentially entered records. Often IDENTITY primary keys. Rarely updated, require reversing entries to undo. Transaction tables generally relate two or more Master tables. Invoice (trainvoice) would relate mascustomer & masproduct. trxInvoice, trxOrder, trxSale

7 History his Used for storing Transaction and Master table History. Source tables generally have identical structure. Records are moved from Transaction tables to History tables at the end of every period. Master records are generally moved as they become obsolete. hisInvoice, hisCustomer, hisProduct

8 Query qry Tables or views used in complex queries. Often used to 'join' an input list from a user instead of using a long 'or' string. qryState

9 Report rpt Tables or views used for the purpose of creating reports. Like Data entry tables, Report tables are often copied to local temporary tables rptCustPhone, rptProduct

10 Export exp Tables or views used to prepare and combine data prior to exporting. expSalesData

11 Import imp Tables used to prepare and combine data prior to importing. Import tables often require a data normalization process prior to inserting into Master or Transaction tables impMainframe

12 Changed chg Subsets of tables changed by update processes. Data warehouse night updates might update master records and the old version from the last run would be here. chgMainframe

13 New new Tables that are under construction or testing. Can be used to roll out any type of new tables. newCustomer

14 Old old Copies of tables that have been replaced. Often used to compare previous versions of tables that were updated in data warehouse apps. oldSalesData

15 Backup bac Temporary backup copies of tables. Use prior to restructuring, etc. These tables are considered as temporary and disposable. These tables are commonly deleted without warning. bacProduct

16 User usr User defined tables or views. When you give users the ability to create and share tables, keep them here. usrBirthday, usrGame, usrBatch

*special function categories can be created for special circumstances.

Home | Business Analyst | Project Database
Data Warehouse | Data Modeling | Microsoft Access

Data Modeling

See how Scott simplifies large databases with more than 500 tables.

When creating every table follow these 4 easy steps:

Additional naming conventions:

Table Functions