In the article below, Alpha developer Doron Farber from www.dFarber.com demonstrates a key aspect of database development, Linking/Control tables, using Alpha Five and MS SQL Server.
Programming ConceptsMany to Many Relationships with Alpha Five
PlatformAlpha Five and MS SQL Server
BackgroundThe customer is a not-for-profit no interest loan management organization which prints and disburses checks to student loan recipients. They then track repayment of the loans after graduation. The students can apply and receive loans to attend multiple institutions, which creates the need to manage the many to many relationships needed between the Students and Institutions tables.
The ConceptThis article demonstrates a design pattern to create a lookup form utilizing a linking table.
The Linking TableA linking or control table is a table that contains linking fields from two or more other tables.
In our example we have a student that participates in multiple courses at various institutions, and, of course other students may also take part in the same courses. In relational database terms the courses are the children of the students. We will elaborate with the example below.
This kind of technique allows you to maintain a normalized data structure in the student records table since you only need to store key fields in the linking table.
To manage Students and Institutions we create 2 grids:
- The first one is the Students grid component - the parent.
- Nest is the Institutions grid - the child.
- The third is an editable Lookup grid where the user can select the institution.
- Students –contains the students’ information
- Institutions – contains institution names.
- StudentsInstitutions - the linking table to hold the students and Institutions related keys.
PK – Primary key which is uniquely identifies each record.
FK – Is the foreign key which copied into the child table and enables you to create relations between the parent and the child table.
|StudentPK||First Name||Last Name||Gender|
|2||Columbus State Community College|
|293||State University Of New York|
|5||New York Institute of Technology|
|7||Arizona State University|
|337||University Of South Florida|
The below was taken from the data modeling tool that we use in our development effort:
*Note - To learn more about database design you may want to check this link: Database design and data modeling.
Linking Table Sample DataLooking at the Students table we can see that Joseph Hays has a StudentPK as 1. He was participating with 2 institutions and their PK is 7 and 337. Looking at the linking table we can see that there are 2 rows to cover these institutions that are marked in green. In order to link Joseph Hays with these 2 institutions we simply copied StudentPK value into the column named StudentFK as 1. Now we have 2 records for this student as follows:
The first InstitutionsFK is 7 and represents: Arizona State University. The second InstitutionsFK is 337 and that represents: University Of South Florida. The InstitutionsFK field is basically the copy of the InstitutionsPK which is found within the Institutions table.
Paul Jones attends only 1 institution and due to that only 1 record was added in the linking table to reflect that. See the turquoise color in the Linking table and this StudentFK is 4.
We can extend the linking table further and will discuss that in the next article.
The Institutions to Students GridBelow is the grid that consist of 2 institutions for the student named: Joseph Hayes. These records are stored in the StudentsInstitutions table which is the linking table. But in Alpha Five I used the property named: Row Expander Grid Content to link the 2 components. The linking fields were: StudentPK to StudentFK and Alpha Five creates the wonder of the linking records.
As shown below we added more fields in the Linking table such as: Graduation Date, College Type, Start Date and End Date. Practically using Alpha Five for date field or the drop down list requires no efforts.
Sample screen shot from the Alpha Five Student Loan Management application in production:
Connecting the Linking Table Into the InstitutionsAs shown in the above component, the question is how do we display the actual Institution name? Since the Institution field is not found within the Linking table. We selected 2 tables StudentsInstitutions and Institutions via the SQL Genie (The SQL Builder). The StudentsInstitutions table is the parent and the the Institutions is the child table.
Below is the SQL statement that creates the connection between the 2 tables via the SQL Genie.
INNER JOIN Institutions
ON StudentsInstitutions.InstitutionsFK = Institutions.InstitutionsPK
ORDER BY StudentsInstitutions.Attending DESC
Creating the Lookup form for the Institutions
See SQL statement for the lookup component to grab all Institutions:
InstitutionsPK AS ID2,
*Note: The reason ID2 is included is to support linking to a detail view or other external content. Since I elected to use a static image to open a detail view you must be able to refer to a field that contains the required linking value (ID2) – not the HTML content that is displayed by the static image definition.
Mapping the Institutions GridWith the StudentsInstitutions grid, check mark the Lookup property against the Institution filed. Click the Lookup definition and you will see the below window. In the Grid name property select the grdInstitutions grid. Click the Field map property and then add the Institution Field in both sides. Also add the InstitutionFK in the left side and in the right side choose the ID2 filed as shown in the below image:
Click the OK button to finalize. That’s basically it.
Display Many to Many Records Using Alpha Five GridFor each student’s record you can see all Institution(s) that are related to each one of them. I left the two foreign keys for the Institution and the Student on purpose on the below grid so you can see all of these values of multiple Institutions that could be linked to multiple students at the same time, but these would be hidden in production.
In the below example Joseph Hays is attending Arizona State University and the foreign key for is 7. The same occurs with Arlene Jordan. They both attend the same Institution and the FK=7 indicate that. The description of the actual Institution is coming from the Institution table. In addition, we keep the integrity of the data and prevent any duplicate record.
Another advantage using this technique is if you change the name of the institution via the lookup grid above, it will be reflected immediately in all students’ records. That will happen to all students that have the same foreign key which was stored in the Linking table. One change in one record can propagate to thousands of records instantly.
ConclusionsThe techniques and design patterns in this article allow us to accomplish the following:
- Preserve the integrity and normalize data - preventing any duplicate record entry.
- One change in record can reflects instantly across all related records.
- The same records can easily be related to other students and viewed at the same time as shown in the above grid.
- Provide simple extensibility - allows the developer to add additional fields in the linking table to meet project requirements with minimal effort.
- Demonstrate the ease with which linking tables can be incorporated into an Alpha Five project.
About the AuthorDoron Farber is the owner of The Farber Consulting Group, Inc. a 20 year old IT firm which currently develops custom database applications for both web and mobile business needs. New applications are being built using Alpha Five and SQL Server. With 30 years of development experience, Doron made the decision to move all new development to the Alpha Five platform. Doron’s firm also provides database design, review, optimization and remote DBA services for MS SQL and MySQL databases.
Doron Can Be Reached AtHis main web site: www.dFarber.com
Alpha Five Development : www.dfarber.com/website-design-development/alpha-five-developer.aspx
Converting a MS Access database application to the Web: www.dfarber.com/website-design-development/convert-access-to-web.aspx