|
3.8 How to Model Keys Keys fall in three groups, primary, secondary and foreign. Primary keys are the columns in the table that define a row. We always work with unique primary keys. Secondary keys are usually not unique and are added to a table to allow optimised queries of the structure. Foreign keys are the columns on a table that give sufficient information to query the primary keys of a related table. The JeeWiz model only handles primary keys and foreign keys at the moment. Secondary keys can be added directly to the database and are more a matter for the database administrator than the application modeler. Primary keys may be made up of meaningful data attributes, which you would store whether they formed part of the key or not, or meaningless information that does nothing except define a unique row. One example of the former might be the brand name of a brand. You can say for a list of brands such as Mars, McDonalds, Hoover, Rolls-Royce, that the name is the unique identifier of the row. Alternatively you can have a meaningless but unique number. One disadvantage of this is that if you get too many conflicts between Grandma's (biscuits), Grandma's (knitting wool), Grandma's (retirement homes), etc, you may have to change the key to include sector as well as brand name. Another advantage of the meaningless number is that if you need to change the brand name for any reason, from say Marathon to Snickers, you don't need to worry about converting all the foreign keys on other tables and historical transaction data to the new name. The meaningless key remains the same and you just change the name in one place. Using a visible brand code is a halfway house solution, but over time people have a tendency to invest meaning into the codes. To model a key from meaningful data, just set the key property to true on one or more attributes (such as brand name, or brand name and sector name). This method can also be used on attributes containing meaningless but visible codes (such as brand code). To create a single meaningless code that is not seen by the user, create an numeric attribute (int or long) and set the autokey property to true. This will increment the key each time a new row is created for you. Because we always need a unique primary key of some sort, if no keys are modeled, the transform will create one for you, an autokey called oid. This is the easiest way to handle primary key creation for new tables. Foreign keys are placed on tables depending on the relationship between the tables. In the case of a one-to-many or many-to-one relationship, one or more columns are added to the table on the many side of the relationship which are filled in with the value of the primary key of the other table. So if an Order table had a one-to-many relationship with an Order_Line table, and the primary key of Order was orderNo, a foreign key column of orderNo would be added to Order_Line. In the case of a many-to-many relationship, foreign key columns corresponding to both ends would be placed on a link table joining the two tables. In the case of a one-to-one relationship, the key is placed on a single end, chosen "randomly" unless otherwise modeled. This randomness has to be taken into account when attaching to tables that have already been created in other applications. There a fifty-fifty chance that the model will pick the wrong end left to itself, so you should model the keys for a one-to-one relationship as though you were working on a legacy system, rather than another JeeWiz generated system. You do not need to add an attribute for every foreign key column. You will normally not map foreign keys at all! If you do need to specify the key field names, you should use a key map instead. If you are using meaningful keys and attaching to a legacy database, you may have an attribute mapped which is also a foreign key column on the table, but this is unnecessary in most circumstances. RSA has no obvious slot for keymaps, so the properties have been placed denormalised against the role/accessor attributes of the ends. They come in three groups of three, allowing a composite key (such as brandName, sector) to be made up of one to three attributes. These should be filled in order, so use keyMap1 before keyMap2 before keyMap3. For each attribute in the primary key of the other table, map you have three properties: foreignKey, foreignKeyDbmsColumn and relatedKey. If you have a foreign key column in the table that you need to map you should enter this in the foreignKeyDbmsColumn property. If the primary key attribute that the foreign key attribute maps to has a different name, you should put the name In the relatedKey field. If there is a meaningful attribute that forms all or part of the foreign key you should use the foreignKey property instead of the foreignKeyDbmsColumn property. For example, if the Brand entity (primary key: brandName) is related to the Product entity (primary key: brandName, productType, size), So the (Mars) Brand related to (Mars, Ice-Cream, 500ml) Product, you would use enter brandName in the foreignKey field on the brand accessor attribute on the Product entity. If the Customer entity (primary key: oid) related one-to-many to Order, you might enter foreignKeyDbmsColumn as "customerFK" and relatedKey as "oid" on the customer accessor on the Order entity, which would create a column on the Order table called customerFk which related to the oid column on customer. Finally if OrderLine (primary key: orderNo, orderLineNumber) related many to one with Product (primary key: brandName, productType, size), you would create a key on the many side, that is on the product accessor of the OrderLine entity, which might be
Top Tips:
Links:
Copyright © 2001-2006 New Technology / enterprise Ltd. | |||||||||||||||||||||
