- Typical Problems with LDAP Schema Design
- An Example
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
An Example
An LDAP schema can be designed that holds the same information as a relational database schema. Using the above recommendations for the same problems can be avoided in LDAP schema designs that are avoided using normalized relational database schemas. Consider the relational database schema in Table 4.1.7
Table 4.1 Sample relational database schema.
|
Table Name |
Column Name |
Syntax |
|
Supplier |
Supplier Number |
Character String |
|
Supplier |
City Name* |
Character String |
|
City Status |
City Name |
Character String |
|
City Status |
Status |
Integer |
|
Part |
Part Number |
Character String |
|
Part |
Part Name |
Character String |
|
Part |
Color |
Character String |
|
Part |
Weight |
Integer |
|
Supplier Shipment |
Supplier Number* |
Character String |
|
Supplier Shipment |
Part Number* |
Character String |
|
Supplier Shipment |
Quantity |
Integer |
This schema represents information about parts that can be ordered from various suppliers. Columns with an asterisk ("*") at the end of their names are nonkey columns that are primary keys of another table. These columns are called foreign keys. A company has offices in various cities and can order parts from various suppliers that have parts available. The different suppliers and their locations are represented in the Supplier table (Table 4.2). The status of each of the company's offices is represented in the City Status table (Table 4.3). Information about the different parts that can be ordered is represented in the Part table (Table 4.4). Finally, the current list of parts that have been ordered from various suppliers is represented in the Supplier Shipment table (Table 4.5). It is assumed that any part can be ordered from any supplier and that each supplier is located in only one city. This schema is already in third normal form.
Table 4.2 Data in the Supplier table
|
Supplier Number |
City |
|
S1 |
London |
|
S2 |
Paris |
|
S3 |
Paris |
|
S4 |
London |
|
S5 |
Athens |
Table 4.3 Data in the City Status table
|
City |
Status |
|
Athens |
30 |
|
London |
20 |
|
Paris |
10 |
|
Rome |
50 |
Table 4.4 Data in the Part table
| Part Number | Part Name | Color | Weight |
|
P1
|
Nut | Red |
12
|
|
P2
|
Bolt | Green |
17
|
|
P3
|
Screw | Blue |
17
|
|
P4
|
Screw | Red |
14
|
|
P5
|
Cam | Blue |
12
|
|
P6
|
Cog | Red |
19
|
Table 4.5 Data in the Supplier Shipment table
|
Supplier Number |
Part Number |
Quantity |
|
S1 |
P1 |
300 |
|
S1 |
P2 |
200 |
|
S1 |
P3 |
400 |
|
S1 |
P4 |
200 |
|
S1 |
P5 |
100 |
|
S1 |
P6 |
100 |
|
S2 |
P1 |
300 |
|
S2 |
P2 |
400 |
|
S3 |
P2 |
200 |
|
S4 |
P2 |
200 |
Assume that the database tables have the information in Tables 4.2 through 4.5. Now, how can we create an LDAP schema to represent this same information? It is straightforward to create an object class that has the same attributes as each table. However, it is crucial to understand the foreign key relationships, as this will help in creating the DIT structure. Whenever possible, if a table holds a foreign key then LDAP entries that are in the object class that correspond to that table should be children of LDAP entries that correspond to the table in which the foreign key is a primary key. For example, the City column of the Supplier table is a foreign key. It is the primary key in the City Status table. Thus, Supplier entries in LDAP should be children of City Status entries. This leads to the first two object class definitions:
( NAME 'cityStatus' SUP top STRUCTURAL MUST (cityName $ status) ) ( NAME 'supplier' SUP top STRUCTURAL MUST supplierNumber )
Notice that the supplier object class does not include the cityName attribute. This is because the attribute is implied by the parent-child relationship and can be retrieved from the DN of the supplier entry. Figure 4.4 illustrates the portion of the resulting DIT that is made up of entries from these two object classes.
)
Figure 4.4 DIT for Supplier and City information.
In Figure 4.4, notice that each supplier entry is a child of the city entry in which it is located. Thus, the DNs for the supplier entries are
SupplierNumber = S1, cityName = London, o =acme.com
supplierNumber = S2, cityName = Paris, o = acme.com
supplierNumber = S3, cityName = Paris, o = acme.com
supplierNumber = S4, cityName = London, o = acme.com
supplierNumber = S5, cityName = Athens, o = acme.com
One of the properties of the relational database schema is that cities can be represented in the database, even when there are no known suppliers in that city. Notice that this representation preserves that property.
Notice too that the Part table has no foreign keys. Its entries can reside at the same level in the DIT as the City entries. But for clarity, they will all be placed in an organizationalUnit entry called ou = parts. Supplier Shipment entries are another matter. It has two foreign keys. One of the foreign keys is chosen as the parent entry in the DIT, while the other is used as a reference. In LDAP object classes references to other entries are always represented as DNs. This leads to the other two object class definitions:
( NAME 'part' SUP top STRUCTURAL MUST (partNumber $ partName $ color $ weight) ) NAME 'supplierShipment' SUP top STRUCTURAL MUST partNumberDN $ quantity ) )
These object classes will use these attribute type definitions:
NAME 'cityName' SUP name ) NAME 'status' SYNTAX 1.3.6.1.4.1.1466.115.121.1.27 ) NAME 'supplierNumber' SUP name ) NAME 'partNumber' SUP name ) NAME 'partName' SUP name ) NAME 'color' SUP name ) NAME 'weight' SYNTAX 1.3.6.1.4.1.1466.115.121.1.27 ) NAME 'partNumberDN' SYNTAX 1.3.6.1.4.1.1466.115.121.1.12 ) NAME 'quantity' SYNTAX 1.3.6.1.4.1.1466.115.121.1.27 )
The syntaxes used in these attribute type definitions are defined in RFC 2252. The complete DIT that corresponds to the information in the relational database defined in Tables 4.2 through 4.5 is shown in Figure 4.5.
)
Figure 4.5 Complete DIT for Supplier database.
Notice that this representation allows for only a single order from each supplier. If there is a requirement for multiple orders from a single supplier, the supplierShipment object class would have an order number attribute. As long as each order number was unique, this new attribute would allow for the creation of multiple orders from a single supplier. Since this was not a requirement in the relational database schema, it was not added as a requirement for the LDAP schema. In the DIT, each supplierShipment entry is a child of the corresponding supplier and has a pointer to the part number that was ordered from the supplier. Searching for various items in this DIT is straightforward. Consider the following problems and the resulting LDAP searches:
-
Find all orders for part number P3
-
Base Object = "o = acme.com," Scope = subtree, Filter = "(&(object-Class = supplierShipment) (partnumberDN = 'partnumber-P3, ou = parts, o = acme.com'))"
-
-
Find all orders for supplier number S2
-
Base Object = "supplierNumber = S2, cityName = Paris, o = acme .com," Scope = single-level, Filter = "objectClass = supplierShipment"
-
When new orders need to be entered into the system, then new supplier-Shipment entries need to be created in the DIT. For example, to add an order for 500 units of part number P4 from supplier number S3, the following Add operation should be initiated.
-
New entry name.—"partnumberDN = 'partnumber-P4, ou = parts, o = acme.com', supplierNumber = S3, cityName = Paris, o = acme.com"
-
Attributes:
-
ObjectClass—Top, supplierShipment
-
Quantity—500
-
Notice that the DN of the part number is used as part of the DN of the sup-plierShipment entry. While this makes for long DNs, it is not illegal. However, for convenience in the real world, order numbers would probably be used as discussed above.