An understanding of the database tables will be developed herein. The following detail is a series of linked tables via Primary Keys referenced in stepped tables.
Notice in Detail 1 below, the table in the top left is the beginning of the series of linked tables. Note the Primary Key is signified by the key symbol. The primary key when listed and linked in a down stream table is the Foreign Key. When the tables are linked in the database table the data is more difficult to delete, though not totally indestructible. In the following tables, consider the table title as in the context of an Excel spreadsheet. The table title (ex. ProjectDataSheet) is the same as a spreadsheet’s tab title. The defined subtitles (child branches off the root) are column headers across the top of the spreadsheet. The Primary Key is one of the column headers in the spreadsheet. With all of my database tables, the primary keys are integers, seeded with 1 to increment by 1 as each record is created, just as is represented with the default row numbers in a spreadsheet. In the spreadsheet and the database table, each row is commonly referred to as a record.
Side Note: The examples shown here and in the Visual Studio programs, specifically the Windows, will follow the general layout akin to PLC (Programmable Logic Controllers) ladder logic programming. The form flow will generally start in the upper left corner and flow right and down. Occasionally the Visual Studio windows deviate from the rule-of-thumb, but the development and programming layout practice is used often.
Description of Tables
Project Data Sheet: Top most table in the links, for this example, which contains the Unique Value of ProjectNoID. Since the business choose to not start with the record ID number (Unique record sequential value) due to another program generating the project numbers, the column ProjectNo was used to transfer the “Non-Linked” reference number. This was able to maintain a loose connection between the two programs. Logic: A Business will have many projects.
Requisition: Linked table with foreign key ProjectNoID . In this example, the two shown columns are all that are necessary for this example. ReqID (Requisition ID #) is the primary key. Logic: A project will have many requisitions.
MaterialList: The Material List table has the Primary Key MaterialListID. Relating to a preceding table, there is also the foreign key ReqID. In the business environment, there will be a redundancy of material. This MaterialList table has additional foreign keys. In this database architecture, the linked tables are not linear, but more of a series of linked tables designed to minimize database growth. MaterialList table shows three (3) foreign keys (columns with “ID” end tags) shown in this example.
Item: this is the list of material that has been accumulating over the years of project and business purchasing. The Item database table has its primary key, IemID, and additional foreign keys. Vendor and Supplier terms are used interchangeably. The default supplier/vendor is assigned, VendorID, and the programming is able to easily change to alternate vendors when needed.
Description of Column Headers
Reviewing each table, notice there are columns with labels ending in “ID“. By default, the ID columns are integers and where the ID is the primary key, the column is the record’s Identity specification, sequential, and automatically increments by 1 (the seed assignment) when new records are created.
Commonly use variable types used are Bit (logical true/false), Date, Float, Integer, Money, and NVARCHAR (string). The following detail is an example of column term definitions.
int: Integer number, positive or negative.
null or not null: in most of the columns except for the primary key, data within the column can be empty.
nvarchar: The character string variable which can me defined with a limited or unlimited (MAX) option. VAR(MAX) has been used in a few instances such as Model and Description in the Item database table. There have been some instances where model number has been 100 characters in length, where this string was defined as VAR(100). Description is often long for some components, but has been limited to briefly identify the component’s relevant characteristics.
money: Though practically a float, money can hold costs to the thousandths of a dollar. Years ago, there was minimal concern about money extending beyond two decimal points. But as the cost for equipment is sometimes shown to five decimal points, then rounded up to common dollar format, the final cost on purchase orders is sometimes not as exactly as expected in the total.
datetime: the exact moment in day and time the column was updated. In the item database table, the LastUpdate column is used to keep track of the last cost update.
NULL: A state or condition of no value applied.
bit: Bit is a binary term usually define as True (1) or False (0). Also, the value of bit can be defined as NULL. When NULL is used in the bit, then there can be three (3) states used in programming.
The following posts will be on communication structures with the database.