The CRM_Demo database is used in LinqConnect samples. This topic describes its structure. DDL and DML scripts for all the supported databases except SQLite are available here – \Program Files\Devart\dotConnect\Linq\Samples\LinqConnect\. For SQLite, a ready-to-use database – crm_demo_sqlite.db – is provided.
CRM_Demo is a simple database for managing activities of a book store. Such database should contain information about the products available, the book store customers (which mainly are companies, but may be individuals as well), personal contacts, and orders being processed. This information is stored in six tables:
The figure below displays a table structure and scheme of CRM_Demo database:
Product Categories
Products (which are books) are classified by several categories (genres and styles). The categories may contain sub-categories and be embedded into major categories. The Product Categories table contains a field for category name and a field for the reference to the parent category (thus the table is self-referential).
Products
The Products table stores book title, reference to its category, price and quantity of a product in the stock. As it was said, our products are books, or rather literature works. Each work may consist of either one or several volumes. This option is determined by the UnitName field which may be set to either 'unit' (for single volumes) or 'parcel' (for multiple volumes) values, and the UnitScale field which specifies the exact number of volumes in a particular work. The last thing about the products is that they can be discontinued, and the price of discontinued products usually is lower. To handle this case the table includes Discontinued and DiscontinuedPrice fields.
Company
The Company table stores an information about customers and shipping contractors of our book store. Companies related to the book store can be either customers or shipping contractors or both. The Company table contains fields for company name, web site, legal or physical addresses, phones and the PrimaryContact field referencing the person who is preferable company contact.
Person Contact
This table stores an information about representatives of the companies that cooperating with the bookstore. Also, this table contains an information about employees of the shipping companies and individuals that purchase books in the bookstore. The table hence should include general contact information, like names, personal and business phones, full address (which can be either home or business), and, if applicable, the reference to the employer company.
Orders
The general information about orders is stored in the Orders table. Those are: references to customer company and person (when the first is null, the customer is an individual, when it is not, the person is a company contact related to this order), order date, ship date, reference to a shipping company, freight cost, and discount. The information about the products ordered is stored in the Order Details table as each order may consist of several books of different titles.
Order Details
Order Details table contains detailed information on what a single order consists of, each record of this table references to the Product table and contains an information about price and quantity products that are ordered. If an order includes several titles, a single row should be created for each title.