[ Pobierz całość w formacie PDF ]
.Also, remember to call Unprepare at the end, to free some BDE resources.Note that some powerful SQL servers can do the same operation by caching the requestsand automatically determining that you are sending the same request twice.If the server issmart enough, preparing the query might result in little or no performance gain.NOTEWhen you write parametric queries against a SQL server, you should consider nullvalues withcare.In fact, to test for a null value, you should not write a field = null test, but use thespecific expression fieldis nullinstead.Elements of Database DesignAlthough this is a book on Delphi programming and not on databases, I feel it s quite impor-tant to discuss a few elements of good (and modern) database design.The reason is simple: ifyour database design is incorrect or convoluted, you ll either have to write terribly complexSQL statements and server-side code, or write a lot of Delphi code to access your data, possi-bly even fighting against the design of the TDataSet class.Entities and RelationsThe classic relational database design approach, based on the entity relation (E-R) model,involves having one table for every entity you need to represent in your database, with onefield for each data element you need plus one field for every one-to-one or one-to-manyCopyright ©2001 SYBEX, Inc., Alameda, CA www.sybex.com2874c14.qxd 7/2/01 4:33 PM Page 605Elements of Database Design 605relation to another entity (or table).For many-to-many relations, instead, you ll need a sepa-rate table.As an example of a one-to-one relation, consider a table representing a university course.This would have a field for each relevant data element (name and description, room where itis held, and so on) plus a single field indicating the teacher.The data of the teacher, in fact,should not be stored within the course data, but in a separate table, as it might be referencedfrom elsewhere.The schedule of each course can include an undefined number of hours in different days,so they cannot be added inside the same table describing the course.Instead, this informa-tion must be placed on a separate table, including all of the schedules, with a field referringto the class each schedule is for.In a one-to-many relation, such as this,  many records ofthe schedule table point back to the same  one record of the course table.A more complex situation is required to store which student is taking which class.Studentscannot be listed directly in the course table, as their number is not fixed, and the classes can-not be stored within the student s data for the same reason.In a similar many-to-many rela-tion, the only approach is to have an extra table representing the relation, which listsreferences to students and courses.Normalization RulesThe classic design principles include a series of so-called normalization rules.The goal ofthese rules is to avoid duplicating data in your database (not only for saving space, but mainlyto avoid ending up with incongruous data).For example, you don t repeat all of the customerdetails in each order, but refer to a separate customer entity.This way you save memory, butwhen the customer details change (for example, because of a change of address) all of theorders of this customer will reflect the new data.Other tables that relate to the same cus-tomer will probably be automatically updated as well.Normalization rules imply using codes for commonly repeated values.For example, if youhave a few different shipment options, you won t use a string-based description for theseoptions within the orders table, but would rather use a short numeric code, mapped to adescription in a separate lookup table.This last rule, which should not be taken to the extreme, is to avoid having to join a largenumber or table for every query.You can either account for some de-normalization (leaving ashort shipment description within the orders table) or use the client program to provide thedescription, again ending up with a formally incorrect database design.This last option ispractical only when you use a single development environment (let s say, Delphi) to accessthis database.Copyright ©2001 SYBEX, Inc., Alameda, CA www.sybex.com2874c14.qxd 7/2/01 4:33 PM Page 606606 Chapter 14 " Client/Server ProgrammingFrom Primary Keys to OIDsIn a relational database, records are not identified by a physical position (as in Paradox andother local databases) but only by the data within the record itself.Typically, you don t needthe data of all of the fields to identify a record, but only a subset of the data, forming the so-called primary key.If the fields that are part of the primary key must identify an individualrecord, their value must be different for each possible record of the table.NOTETechnically, many database servers add internal record identifiers to the tables, but this hap-pens only for internal optimizations and has little to do with the logical design of a relationaldatabase.Also, these internal identifiers work differently in different SQL servers and mighteven change among versions, a good reason not to rely on them.The early incarnations of the relational theory dictated the use of logical keys, which meansselecting one or more records that indicate an entity without risk of any confusion.This isoften easier to say than to accomplish.For example, company names are not generally unique,and even the company name and its location don t provide a complete guarantee.Moreover, ifa company changes its name (not an unlikely event, as Borland can teach us) or its location,and you have references to the company within other tables, you must change all those refer-ences as well, with the risk of ending up with dangling references.For this reason, and also for efficiency (using strings for references implies using a lot ofspace in secondary tables, where references often occurs), logical keys have been invariablyphased out for physical or surrogate keys.Physical keys refer to a single field of the table iden-tifying an element in a unique way.For example, each person in the U.S.has a Social Securitynumber (SSN), but almost every country has a tax ID or other government-assigned numberthat identifies each person.The same typically exists for companies.Although these ID numbersare guaranteed to be unique, they might change depending on the country (creating troublesfor the database of a company also selling its goods abroad) or even within a single country (toaccount for new tax laws).They are also often inefficient, as they might be quite large (Italy, forexample, uses a 16-character code, letters and numbers, to identify people).Another common approach is to use surrogate keys, which are basically numbers identifyingeach record, in the form of client codes, order numbers, and so on.These surrogate keys arecommonly used in database design.However, in many cases, these end up being some sort oflogical identifiers, with client codes showing up all over the places, not a great idea overall.Copyright ©2001 SYBEX, Inc., Alameda, CA www.sybex.com2874c14 [ Pobierz caÅ‚ość w formacie PDF ]