Membership Database Schema for SQL Server

System Requirements

• Azure SQL Database, or
• Microsoft SQL Server 2012 and higher, or
• LocalDB 2012 and higher

Naming Conventions

• Singular UpperCamelCase names for tables. E.g. UserLogin
• Singular UpperCamelCase names for columns. E.g. PhoneNumber
• Always try to use names that make sense and are descriptive of their purpose
• Single column primary key fields should be named Id
• Use the following pattern for constraints and indexes: {suffix}_{tablename}_{columnname(s)} where the suffix is one of the following:
  • PK for a Primary Key constraint
  • AK for a Unique constraint / Alternate Key
  • IX for any other kind of index
  • FK for a Foreign Key
  • CK for a Check constraint
  • DF for a Default constraint

References

T-SQL Querying (Developer Reference) by Itzik Ben-Gan, Adam Machanic, Dejan Sarka, Kevin Farlee; Microsoft Press (Mar 2015) Squeeze maximum performance and efficiency from every T-SQL query you write or tune. Four leading experts take an in-depth look at T-SQL’s internal architecture and offer advanced practical techniques for optimizing response time and resource usage. Emphasizing a correct understanding of the language and its foundations, the authors present unique solutions they have spent years developing and refining. All code and techniques are fully updated to reflect new T-SQL enhancements in Microsoft SQL Server 2014 and SQL Server 2012.

Microsoft SQL Server 2012 Internals (Developer Reference) by Kalen Delaney, Bob Beauchemin, Conor Cunningham, Jonathan Kehayias, Paul S. Randal, Benjamin Nevarez; Microsoft Press (Nov 2013) Explore the core engine of Microsoft SQL Server 2012—and put that practical knowledge to work. Led by a team of SQL Server experts, you’ll learn the skills you need to exploit key architectural features. Go behind the scenes to understand internal operations for creating, expanding, shrinking, and moving databases—whether you’re a database developer, architect, or administrator.

• Microsoft SQL Server Documentation on MSDN
• User Profiles Data Model on DatabaseAnswers.org
• Table Naming Dilemma: Singular vs. Plural Names on StackOverflow.com

License

Copyright © 2016 Membership Database contributors. This source code is licensed under the MIT license found in the LICENSE.txt file. The documentation to the project is licensed under the CC BY-SA 4.0 license.