c# generate barcode Figure 11-2. Poor table design to store more than one team for a member in Font

Generating PDF-417 2d barcode in Font Figure 11-2. Poor table design to store more than one team for a member

Figure 11-2. Poor table design to store more than one team for a member
PDF417 Drawer In None
Using Barcode drawer for Font Control to generate, create PDF 417 image in Font applications.
www.OnBarcode.com
Drawing ECC200 In None
Using Barcode creator for Font Control to generate, create ECC200 image in Font applications.
www.OnBarcode.com
C HA PTER 11 C OMMON PROBLEMS
Paint Code 128 Code Set C In None
Using Barcode drawer for Font Control to generate, create Code 128 Code Set A image in Font applications.
www.OnBarcode.com
Denso QR Bar Code Creator In None
Using Barcode encoder for Font Control to generate, create QR Code 2d barcode image in Font applications.
www.OnBarcode.com
Now suppose we are asked to find those members in TeamA. You can immediately see the problem. Melissa has TeamA in the Team1 column, and Brenda has TeamA in the Team3 column. We need to check every team column for the existence of TeamA. This isn t that difficult; the query in Listing 11-2 will do the trick.
Printing EAN128 In None
Using Barcode drawer for Font Control to generate, create GTIN - 128 image in Font applications.
www.OnBarcode.com
Print Code 39 In None
Using Barcode generation for Font Control to generate, create Code 39 Full ASCII image in Font applications.
www.OnBarcode.com
Listing 11-2. Finding Members of TeamA SELECT * FROM Member WHERE Team1 = 'TeamA' OR Team2= 'TeamA' OR Team3 = 'TeamA'
PDF-417 2d Barcode Maker In None
Using Barcode printer for Font Control to generate, create PDF 417 image in Font applications.
www.OnBarcode.com
USS Code 93, USS 93 Drawer In None
Using Barcode drawer for Font Control to generate, create Code 93 image in Font applications.
www.OnBarcode.com
While we can extract the information we require, the design in Figure 11-2 is clearly not the best way to store it. We will certainly start encountering problems if we need queries like Find members who are in both TeamA and TeamB or Find members who are in more than two teams. You could probably devise queries that would answer these questions, but at this point, I would ask for the database to be redesigned properly before trying to fulfill such requests. What is required is an intermediate Membership table to record relationships between members and teams. This is very like the Entry table, which records relationships between members and tournaments. The Membership table would look something like Figure 11-3.
Generating PDF 417 In VS .NET
Using Barcode encoder for Reporting Service Control to generate, create PDF417 image in Reporting Service applications.
www.OnBarcode.com
Encoding PDF-417 2d Barcode In Java
Using Barcode drawer for Java Control to generate, create PDF417 image in Java applications.
www.OnBarcode.com
Figure 11-3. A Membership table that records the relationship between members and teams
Creating Barcode In None
Using Barcode creator for Software Control to generate, create Barcode image in Software applications.
www.OnBarcode.com
Read Data Matrix In None
Using Barcode reader for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
With the additional Membership table, we can now use all the relational operations, as described in previous chapters, to easily answer questions like Who is in TeamA and TeamB and Who is in three or more teams We can create a Membership table with the SQL in Listing 11-3. The code includes foreign key constraints to the existing Member and Team tables and a concatenated primary key.
Making Code39 In Objective-C
Using Barcode creation for iPhone Control to generate, create Code 39 image in iPhone applications.
www.OnBarcode.com
EAN / UCC - 13 Maker In Objective-C
Using Barcode generator for iPhone Control to generate, create GTIN - 128 image in iPhone applications.
www.OnBarcode.com
Listing 11-3. Creating a New Membership Table CREATE TABLE Membership ( MemberID INT FOREIGN KEY REFERENCES Member, Team Char(20) FOREIGN KEY REFERENCES Team, PRIMARY KEY (MemberID, Team) )
Decoding ANSI/AIM Code 39 In None
Using Barcode reader for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Data Matrix ECC200 Decoder In .NET Framework
Using Barcode recognizer for VS .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
CHAPTER 11 C OM MON PROBLE MS
2D Creator In Visual Basic .NET
Using Barcode maker for .NET framework Control to generate, create Matrix Barcode image in .NET applications.
www.OnBarcode.com
GS1-128 Recognizer In VB.NET
Using Barcode scanner for .NET framework Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
If you don t mind a bit of manual fiddling about, you can populate the table with repeated update queries like the one in Listing 11-4. The query finds each member who is in TeamA and creates an appropriate row in the Membership table. If there are not too many teams, you can manually alter the second and last lines of the query for each team (TeamA, TeamB, and so on) and create a Membership table quite quickly. You then need to delete the Team columns from the Member table in Figure 11-2, and the database will look a whole lot better.
EAN / UCC - 13 Generator In Java
Using Barcode generator for Java Control to generate, create EAN128 image in Java applications.
www.OnBarcode.com
Making Code 39 Extended In None
Using Barcode maker for Online Control to generate, create Code-39 image in Online applications.
www.OnBarcode.com
Listing 11-4. Creating Rows in the Membership Table for TeamA INSERT INTO Membership (MemberID, Team) SELECT MemberID, 'TeamA' FROM Member WHERE Team1 = 'TeamA' OR Team2= 'TeamA' OR Team3 = 'TeamA'
No Keys
The previous section gave an example of the problems you can run into if the underlying database has the inappropriate tables for your query. You sometimes find that the database has the appropriate tables, but without any primary or foreign key constraints. In these cases, you can run your queries, but the underlying data is likely to be inconsistent. In this section, you will see how you can find some of the inconsistencies by running a few queries. Suppose that the Membership table in Figure 11-3 had been created without a primary key. Then we run the risk of having duplicate rows. For example, we might have two identical rows for member 118 in TeamA, which would cause a query that counts the number of members in TeamA to give the wrong result. If you try to add a primary key when duplicates already exist, you will get an error, which is one way to find where the problems are! Another way is to do a GROUP BY query on the fields that should be in the primary key and use a HAVING clause to find those with two or more entries, as in Listing 11-5.
Listing 11-5. Finding Rows with Duplicate Values in the Potential Primary Key SELECT MemberID, Team, Count(*) FROM Membership GROUP BY MemberID, Team HAVING Count(*) > 1
Listing 11-5 will find the problem rows. When the table has fields other than the primary key fields, you need to manually inspect the values in those columns to decide which row should be deleted. The Membership table, which has only primary key fields, causes a different problem. How do we delete just one copy of the row for member 118 in TeamA Because
Copyright © OnBarcode.com . All rights reserved.