Lec07 - Internal External Data Design.pptx

Suppose products are uniquely identified by the

Info icon This preview shows pages 6–11. Sign up to view the full content.

View Full Document Right Arrow Icon
Suppose products are uniquely identified by the combination of “Product Type” and “Model Number”, and “Version Number”. Product information relates everything in the company – production, inventory, marketing, accounting, etc. If these 3 fields are used as a PK, every time another tables is related to a product, it will need to store all three of these fields as link information. Adding a “Product ID” field (say an autonumber field) to product records and making it the primary field greatly reduces the complexity and storage requirements for relating tables. The tradeoff is that may need to code additional logic for insuring unique product records are being added, since the DBMS may no longer check my original key.
Image of page 6

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 Decide data types & sizes Can be specified in ANSI "generic" data types at this stage if DBMS has not been selected The data type should minimize the storage space needed (i.e., smallest representation that represents all possible values), taking into account growth The data type should improve data integrity The data type should support all intended data manipulations Should consider native CPU register sizes (e.g. , 32, 64, 128 bits) if speed is critical.
Image of page 7
Internal Data Design Field Domain Constraints (continued) Specify formats Date/times - short or long date formats, etc. Strings – input masks, min/max lengths Numeric – numeric format ($ or currency symbol, thousands separators, number of decimal points), input masks Specify constraints on range or allowed values Validation rules Limit to list for lookups Specify whether field values must be unique
Image of page 8

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

View Full Document Right Arrow Icon
Internal Data Design Fields Domain Constraints (continued) Specify whether field values are required (can value be null or not; i.e., must a value be entered?) Allowing Null values must be considered carefully. Null values may require special flagging / handling when generating reports, mailing labels, bar code labels, etc. For instance, shipping an order to an address without a zip code can risk the order being returned. Also, Null values can be headaches in coding & queries. Depending on the DBMS and its settings, the Boolean expression (Null=2) may evaluate to true, and (Null=Null) to false). This can lead to programming errors.
Image of page 9
Internal Data Design Field Domain Constraints (continued) Decide on Default Values Use default values only where makes sense. For instance, many times numeric fields (e.g., price, tax, total, quantity) can safely (and should) have “0” or “0.0” as their defaults. However, a default of 0 would be potentially dangerous for ID or foreign key fields, and should be avoided.
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
Image of page 11
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