Lec07 - Internal External Data Design.pptx

Default values can become a crutch short cut for data

Info icon This preview shows pages 10–13. Sign up to view the full content.

View Full Document Right Arrow Icon
Default values can become a crutch / short cut for data entry, leading to data quality problems.
Image of page 10

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Internal Data Design Field Domain Constraints (continued) Decide on whether to store Calculated Fields Decisions need to be made about the tradeoffs between response time, data storage, and currency of calculated fields. If a calculation takes considerable time to calculate (say, it’s the result of an optimization or scheduling algorithm) and the underlying data does not change frequently, then it makes sense to actually store calculated results in a table field. Thought has to be given to when / how often field should be updated. If very rapid response is needed, it again may pay to have a calculated field. A credit card balance is an example. I want to very rapidly reply to a merchants request for approval, but may have to query thousands of records to do the calculation. Having a stored “balance” field would save time. However, if a customer buys a boat, a car, a mink coat, a diamond ring, and a new surround sound multimedia home theater system on separate transactions all within 5 minutes, and I update the calculation only once every 10 minutes, I may have a problem. The rapidly increasing speed of computers is providing the ability to calculate "on the fly" values which previously would require off-line calculation and storing.
Image of page 11
Internal Data Design Design Fields, & Specify Domain Constraints (continued) Decide on Data Coding It can sometimes be useful to replace strings with numeric codes internally in the database, particularly if the strings represent some type of category information. For instance, I may have a product type text field – possible values are “DESKTOP”, “LAPTOP”, “MINI- TOWER”, "TOWER", etc. To allow for future product names, I would need a text field of about 20 characters in length in each record for every product model I make. If instead I used an auxiliary table to assign a numeric code, and assuming I’ll never have over 256 unique product types, I could then use a byte field to represent the same information (i.e., “DESKTOP” becomes 1, “LAPTOP” becomes 2, etc.) The Access Lookup Wizard uses the data coding approach Sorting, searching, filtering, and updates are far faster on numeric codes than on variable length strings. While coding generally makes sense, there is a tradeoff in that any report or form that must show the text version of the code will require one or more join operations. This generates some extra overhead (generally bearable) to the report generation, and also increases the complexity of the information system by adding additional tables and relations. These extra tables are generally coined " lookup tables " or " support " tables.
Image of page 12

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern