Unstructured tables and Foreign Keys

by Albert Anthony D. Gavino

To be able to data mine correctly, one must have organized tables and structures, usually designed by the Database Administrator (DBA).

One of my worst nightmares is dealing with Manual tables and files coming from excel that is, not only did this bring me a problem of efficiency, the design of its foreign key was also faulty, imagine a foreign key that resets every term and every school year, hence the problem of creating Schemas for the data structure.

let me lecture on Important aspects of tables

if your foreign key repeats itself and its status, create another foreign key that will not repeat, but this key should also be reflected in your other tables so that you will be able to connect them altogether.

say for example:

SCHOOLYEAR+TERM+STATUS+CASENO

• 2011-2012-1-FR-1234 (freshman)
• 2011-2012-1-TR-1234 (transferee)
• 2011-2012-1-UG-1234 (2nd UG degree)

SQL Server 2008

1. Primary Key Constraint: Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.
2. Foreign Key Constraint: When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.
3. Default Constraint: Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.