I have been asked by some people interested in SQL and its design to explain a relational database model. I will try to do that here.

The following data model is designed to hold information relating to Motor Vehicle Insurance Policies. For this scenario we need to define the following facts:

These facts define the requirements which the Database must meet and should be agreed between the Database User and the Database Designer prior to physical creation.

An insurance company writes policies for drivers. One policy can cover many drivers and also many vehicles, but a vehicle can be related to only one policy. Drivers can share one or more vehicles (e.g. a husband and wife own one vehicle and they both drive the same vehicle or a family can have multiple vehicles).

The company gets a master list of violations from the Department of Motor Vehicles. These violations are then input into the system and used to determine the price of the policy. A driver may commit more than one violation. One or more drivers can commit the same violation. The system should keep a track of all customers - active (with insurance) and inactive (held in an archive – for canceled customers). All customers should be able to get a quote, insurance or cancel the insurance.

The draft facts have been defined as:

The Entities required should include:

• Drivers
• Vehicles
• Policies
• Insurance Groups
• Violations
• Link_VehiclesDrivers
• Link_ViolationsDrivers

The Entities are related as follows:

• The relationship between the tblVehicles and tblDrivers tables is Many-To-Many (a vehicle may be driven by one or more drivers; a driver may drive one or more vehicles), so a link table should be created (e.g. tblLink_VechiclesDrivers).
• The relationship between the tblVehicles and tblInsuranceGroups tables is One-To-Many (a vehicle may belong to only one insurance group; many vehicles can belong to the same or different insurance groups).
• The relationship between the tblViolations and tblDrivers tables is Many-To-Many (a driver may commit one or more violations; a violation may be commited by one or more drivers), so a link table should be created (e.g. tblLink_ViolationsDrivers).
• The relationship between the tblPolices and tblVehicles is tables is One-To-Many (a policy can cover one or more vehicles; a vehicle can be covered and related to only one policy).

When asking questions of the database we may need to know:

1. How many violations has Driver 'X' had
2. Has Driver 'X' previously been insured with us
3. What insurance group is [car type here]
4. When does Driver 'X's Policy run out.

Lets examine what the table structure might look like:

Reports index stats, index size+rows, member seek + include columns as two comma separated output columns, and index usage stats for one or more tables and/or schemas. Flexible parameterized sorting.
Has all the output of Util_ListIndexes plus the usage stats.

Required Input Parameters
none

Optional
@SchemaName sysname='' Filters schemas. Can use LIKE wildcards. All schemas if blank. Accepts LIKE Wildcards.
@TableName sysname='' Filters tables. Can use LIKE wildcards. All tables if blank. Accepts LIKE Wildcards.
@Sort Tinyint=5 Determines what to sort the results by:
Value Sort Columns
1 Score DESC, user_seeks DESC, user_scans DESC
2 Score ASC, user_seeks ASC, user_scans ASC
3 SchemaName ASC, TableName ASC, IndexName ASC
4 SchemaName ASC, TableName ASC, Score DESC
5 SchemaName ASC, TableName ASC, Score ASC

Usage:
EXECUTE Util_IndexUsage 'dbo', 'order%', 5

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.Util_IndexUsage', 'P') IS NOT NULL DROP PROCEDURE dbo.Util_IndexUsage
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_IndexUsage
By Jesse Roberge - YeshuaAgapao@Yahoo.com
Update - Fixed existance check for drop

Reports index stats, index size+rows, member seek + include columns as two comma separated output columns, and index usage stats for one or more tables and/or schemas.  Flexible parameterized sorting.
Has all the output of Util_ListIndexes plus the usage stats.

Required Input Parameters
    none

Optional
    @SchemaName sysname=''        Filters schemas.  Can use LIKE wildcards.  All schemas if blank.  Accepts LIKE Wildcards.
    @TableName sysname=''        Filters tables.  Can use LIKE wildcards.  All tables if blank.  Accepts LIKE Wildcards.
    @Sort Tinyint=5                Determines what to sort the results by:
                                    Value    Sort Columns
                                    1        Score DESC, user_seeks DESC, user_scans DESC
                                    2        Score ASC, user_seeks ASC, user_scans ASC
                                    3        SchemaName ASC, TableName ASC, IndexName ASC
                                    4        SchemaName ASC, TableName ASC, Score DESC
                                    5        SchemaName ASC, TableName ASC, Score ASC

Usage:
    EXECUTE Util_IndexUsage 'dbo', 'order%', 5

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/

CREATE PROCEDURE dbo.Util_IndexUsage
    @SchemaName SysName='',
    @TableName SysName='',
    @Sort tinyint=5
AS

SELECT
    sys.schemas.schema_id, sys.schemas.name AS schema_name,
    sys.objects.object_id, sys.objects.name AS object_name,
    sys.indexes.index_id, ISNULL(sys.indexes.name, '---') AS index_name,
    partitions.Rows, partitions.SizeMB,
    sys.indexes.type, sys.indexes.type_desc,
    sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint, sys.indexes.is_unique,
    ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
    ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include,
    ISNULL(sys.dm_db_index_usage_stats.user_seeks,0) AS user_seeks, ISNULL(sys.dm_db_index_usage_stats.system_seeks,0) AS system_seeks,
    ISNULL(sys.dm_db_index_usage_stats.user_scans,0) AS user_scans, ISNULL(sys.dm_db_index_usage_stats.system_scans,0) AS system_scans,
    ISNULL(sys.dm_db_index_usage_stats.user_lookups,0) AS user_lookups, ISNULL(sys.dm_db_index_usage_stats.system_lookups,0) AS system_lookups,
    ISNULL(sys.dm_db_index_usage_stats.user_updates,0) AS user_updates, ISNULL(sys.dm_db_index_usage_stats.system_updates,0) AS system_updates,
    (
        (
            (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_seeks,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_seeks,0)))*10
            + CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_scans,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_scans,0)))*1 ELSE 0 END
            + 1
        )
        /CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_updates,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_updates,0))+1) ELSE 1 END
    ) AS Score
FROM
    sys.objects
    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
    JOIN sys.indexes ON sys.indexes.object_id=sys.objects.object_id
    JOIN (
        SELECT
            object_id, index_id, SUM(row_count) AS Rows,
            CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
        FROM sys.dm_db_partition_stats
        GROUP BY object_id, index_id
    ) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
    CROSS APPLY (
        SELECT
            LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
            LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
        FROM
            (
                SELECT
                    (
                        SELECT sys.columns.name + ', '
                        FROM
                            sys.index_columns
                            JOIN sys.columns ON
                                sys.index_columns.column_id=sys.columns.column_id
                                AND sys.index_columns.object_id=sys.columns.object_id
                        WHERE
                            sys.index_columns.is_included_column=0
                            AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
                        ORDER BY key_ordinal
                        FOR XML PATH('')
                    ) AS index_columns_key,
                    (
                        SELECT sys.columns.name + ', '
                        FROM
                            sys.index_columns
                            JOIN sys.columns ON
                                sys.index_columns.column_id=sys.columns.column_id
                                AND sys.index_columns.object_id=sys.columns.object_id
                        WHERE
                            sys.index_columns.is_included_column=1
                            AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
                        ORDER BY index_column_id
                        FOR XML PATH('')
                    ) AS index_columns_include
            ) AS Index_Columns
    ) AS Index_Columns
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ON
        sys.indexes.index_id=sys.dm_db_index_usage_stats.index_id AND sys.indexes.object_id=sys.dm_db_index_usage_stats.object_id
        AND sys.dm_db_index_usage_stats.database_id=DB_ID()
WHERE
    sys.objects.type='u'
    AND sys.schemas.name LIKE CASE WHEN @SchemaName='' THEn sys.schemas.name ELSE @SchemaName END
    AND sys.objects.name LIKE CASE WHEN @TableName='' THEn sys.objects.name ELSE @TableName END
ORDER BY
    CASE @Sort
        WHEN 1 THEN
            (
                (
                    (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_seeks,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_seeks,0)))*10
                    + CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_scans,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_scans,0)))*1 ELSE 0 END
                    + 1
                )
                /CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_updates,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_updates,0))+1) ELSE 1 END
            )*-1
        WHEN 2 THEN
            (
                (
                    (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_seeks,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_seeks,0)))*10
                    + CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_scans,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_scans,0)))*1 ELSE 0 END
                    + 1
                )
                /CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_updates,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_updates,0))+1) ELSE 1 END
            )
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.schemas.name
        WHEN 4 THEN sys.schemas.name
        WHEN 5 THEN sys.schemas.name
        ELSE NULL
    END,
    CASE @Sort
        WHEN 1 THEN CONVERT(VarChar(10), sys.dm_db_index_usage_stats.user_seeks*-1)
        WHEN 2 THEN CONVERT(VarChar(10), sys.dm_db_index_usage_stats.user_seeks)
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.objects.name
        WHEN 4 THEN sys.objects.name
        WHEN 5 THEN sys.objects.name
        ELSE NULL
    END,
    CASE @Sort
        WHEN 1 THEN sys.dm_db_index_usage_stats.user_scans*-1
        WHEN 2 THEN sys.dm_db_index_usage_stats.user_scans
        WHEN 4 THEN
            (
                (
                    (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_seeks,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_seeks,0)))*10
                    + CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_scans,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_scans,0)))*1 ELSE 0 END
                    + 1
                )
                /CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_updates,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_updates,0))+1) ELSE 1 END
            )*-1
        WHEN 5 THEN
            (
                (
                    (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_seeks,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_seeks,0)))*10
                    + CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_scans,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_scans,0)))*1 ELSE 0 END
                    + 1
                )
                /CASE WHEN sys.indexes.type=2 THEN (CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.user_updates,0))+CONVERT(Numeric(19,6), ISNULL(sys.dm_db_index_usage_stats.system_updates,0))+1) ELSE 1 END
            )
        ELSE NULL
    END,
    CASE @Sort
        WHEN 3 THEN sys.indexes.name
        ELSE NULL
    END
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

In questions of science, the authority of a thousand is not worth the humble reasoning of a single individual.
Galileo Galilei

You have enemies? Good. That means you've stood up for something, sometime in your life
Winston Churchill

Play for more than you can afford to
lose and you will learn the game.
Winston Churchill

Patience and the mulberry leaf becomes a silk gown.
Chinese Proverb

Never say more than is necessary.
Richard Brinsley Sheridan
You must be the change you wish to see in the world.
Mahatma Ghandi

There comes a time when one must take a position that is neither safe, nor politic, nor popular, but he must take it because his conscience tells him it is right....
Martin Luther

That which does not kill you makes you stronger.
Neitzsche

It is better to light one candle than to curse the darkness.
Chinese Proverb

Anyone who has never made a mistake
has never tried anything new.
Albert Einstein

We always strive after what is forbidden, and desire the things refused us.
Ovid

The Athenians, alarmed at the internal decay of their Republic, asked Demosthenes what to do.
His reply: "Do not do what you are doing now."
Joseph Ray

If you don't know where you are going,
you'll end up some place else.
Yogi Berra

When one door closes another opens. But often we look so long so regretfully upon the closed door that we fail to see the one that has opened for us.
Helen Keller

There is time for everything.
Thomas A. Edison

Every generation laughs at the old fashions,
but religiously follows the new.
Henry David Thoreau

I demolish my bridges behind me...then there is no choice but to move forward.
Firdtjof Nansen

Nothing can bring you peace but yourself.
Ralph Waldo Emerson
The worst thing you can try to do is cling to something that is gone, or to recreate it.
Johnette Napolitano

You cannot step twice into the same river, for other waters are continually flowing on.
Heraclitus

Happiness does not depend on outward things, but on the way we see them.
Leo Tolstoy

The art of living lies less in eliminating our troubles than in growing with them.
Bernard M. Baruch

The shortest way to do many things is to do only one thing at a time.
Richard Cech

Imagination is more important than knowledge.
Albert Einstein

We don't live in a world of reality,
we live in a world of perceptions.
Gerald J. Simmons

The first and greatest commandment is,
Don't let them scare you.
Elmer Davis