[ Pobierz całość w formacie PDF ]
.The ID columns all canbecome integers, as they should simply be numeric values without decimal places.All thecolumns that contain textual values (the type of user, the city where an office is located, the38 Building Java"! Enterprise Applications Volume I: Architectureuser's first name, and so on) can become VARCHAR columns.This allows them to contain text,but by avoiding the CHAR type, no unnecessary spaces are added to the columns' contents.Theone exception to this is in the state column for offices.I'd recommend using two-letterabbreviations for all 50 states within the U.S., and since two characters are always needed,using the CHAR data type is appropriate.Another simple decision is which columns can have null values and which cannot.In thecase of the user store, every single column should be required (you will see some optionalcolumns when I get to the accounts store).The user's name, information about offices anduser types, and relations between the tables are all required pieces of information.We have already discussed and diagrammed the relationships between the various tables, andprimary and foreign key constraints will put these relationships into action.The scripts inExamples Example 3-1 and 3-2 include these constraints.Be sure that your database supportsreferential integrity; if it doesn't, make the changes indicated in Appendix A.In the case of theForethought database, referential integrity will ensure that users are not assigned tononexistent user types, for example.It also will help when deleting an office if it wasrelocated or the company was downsized.You can easily make changes to the employeesaffected by this change (those in the deleted office) when referential integrity is in place.Onthe other hand, if this feature is not supported by your database, costly searches through allusers in the database have to be performed in such cases.While databases that do not supportforeign key constraints are great for debugging, prototyping, and in particular forexperimenting (for example, on a laptop in an airplane), they are rarely suitable for productionapplications.The final detail to point out is that I do not recommend creating a column for the user'susername.Remember that I discussed storing usernames, passwords, and authentication datain the Forethought directory server, instead of the database.However, the rest of the userinformation is stored in the database.What you need, then, is a way to relate user informationin the database with the same user's data in the directory server.While there is nothing to bedone at a physical level, some programmatic constraints can be put in place with a little[1]planning.To facilitate implementing these constraints, you can add a column to your USERStable in the database called USER_DN.This will store the distinguished name (DN) of the userin the LDAP directory server.The user's DN in this arena serves as a unique identifier, andcan be used to bridge the information gap between the database and directory server.Javacode can then locate a user in the database by using the LDAP DN, or locate a user in thedirectory server by using the USER_DN column of the USERS table in the database.With data types, relationships, and a link between the database and directory server decidedupon, you're ready to create the database schema.Example 3-1 shows the completed SQLscript for creating the discussed tables and relationships.1Although there is no way to relate databases to directory servers yet, companies like Oracle may provide this means soon.Because Oracle 8/9i andother "all-in-one" products of that nature often contain a database and directory server in the same package, it would not be surprising to see theserelationships between differing physical data stores become available.39 Building Java"! Enterprise Applications Volume I: ArchitectureExample 3-1
[ Pobierz całość w formacie PDF ]