Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    confusion about Microsoft's CRM Relational Model

    I was looking at Microsofts databae schema for its CRM product. (http://msdn.microsoft.com/library/de...ectschemas.asp)

    What I don't understant is why they use 3 columns for Address-1, 2 and 3,
    in case a person has 3 address. Instead why won't they use an Address table, so that we won't endup with several NULL columns if a person has one address and on the other hand no where to save the 4th address for a contact?
    Or there is a field for FullName. Why can't we have a view that creates the fullname on the fly instead of saving it in a field. Or the same for the Total amount of a Sales order, why save it in a field instead of creating a view that calculates it (isn't a trigger more load on the database than a calculation on a view?)
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I can't read the documents at that URL for some reason. But are you sure they are 3 addresses, and not 3 address lines? As in:

    Address Line 1: 10 Downing Street
    Address Line 2: Westminster
    Address Line 3: London

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    yes: here are the columns


    1 contactid primarykey no yes yes no
    2 defaultpricelevelid lookup yes yes yes yes
    3 customersizecode picklist yes yes yes yes
    4 customertypecode picklist yes yes yes yes
    5 preferredcontactmethodcode picklist yes yes yes yes
    6 leadsourcecode picklist yes yes yes yes
    8 originatingleadid lookup yes yes yes no
    9 owningbusinessunit lookup yes no yes no
    11 paymenttermscode picklist yes yes yes yes
    12 shippingmethodcode picklist yes yes yes yes
    14 accountid lookup yes yes yes yes
    15 participatesinworkflow bit yes yes yes yes
    16 isbackofficecustomer bit yes yes yes yes
    17 salutation nvarchar 100 yes yes yes yes
    18 jobtitle nvarchar 100 yes yes yes yes
    19 firstname nvarchar 50 yes yes yes yes
    20 department nvarchar 100 yes yes yes yes
    21 nickname nvarchar 50 yes yes yes yes
    22 middlename nvarchar 50 yes yes yes yes
    23 lastname nvarchar 50 yes yes yes yes
    24 suffix nvarchar 10 yes yes yes yes
    25 yomifirstname nvarchar 150 yes yes yes yes
    26 fullname nvarchar 160 yes no yes no
    27 yomimiddlename nvarchar 150 yes yes yes yes
    28 yomilastname nvarchar 150 yes yes yes yes
    29 anniversary datetime yes yes yes yes
    30 birthdate datetime yes yes yes yes
    31 governmentid nvarchar 50 yes yes yes yes
    32 yomifullname nvarchar 450 yes no yes no
    33 description ntext yes yes yes yes
    34 employeeid nvarchar 50 yes yes yes yes
    35 gendercode picklist yes yes yes yes
    36 annualincome money yes yes yes yes
    37 haschildrencode picklist yes yes yes yes
    38 educationcode picklist yes yes yes yes
    39 websiteurl nvarchar 200 yes yes yes yes
    40 familystatuscode picklist yes yes yes yes
    41 ftpsiteurl nvarchar 200 yes yes yes yes
    42 emailaddress1 nvarchar 100 yes yes yes yes
    43 spousesname nvarchar 100 yes yes yes yes
    44 assistantname nvarchar 100 yes yes yes yes
    45 emailaddress2 nvarchar 100 yes yes yes yes
    46 assistantphone nvarchar 50 yes yes yes yes
    47 emailaddress3 nvarchar 100 yes yes yes yes
    48 donotphone bit yes yes yes yes
    49 managername nvarchar 100 yes yes yes yes
    50 managerphone nvarchar 50 yes yes yes yes
    51 donotfax bit yes yes yes yes
    52 donotemail bit yes yes yes yes
    53 donotpostalmail bit yes yes yes yes
    54 donotbulkemail bit yes yes yes yes
    55 donotbulkpostalmail bit yes yes yes yes
    56 accountrolecode picklist yes yes yes yes
    57 territorycode picklist yes yes yes yes
    59 creditlimit money yes yes yes yes
    60 createdon datetime yes no yes no
    61 creditonhold bit yes yes yes yes
    62 createdby lookup yes no yes no
    63 modifiedon datetime yes no yes no
    64 modifiedby lookup yes no yes no
    66 numberofchildren int yes yes yes yes
    67 childrensnames nvarchar 255 yes yes yes yes
    69 mobilephone nvarchar 50 yes yes yes yes
    70 pager nvarchar 50 yes yes yes yes
    71 telephone1 nvarchar 50 yes yes yes yes
    72 telephone2 nvarchar 50 yes yes yes yes
    73 telephone3 nvarchar 50 yes yes yes yes
    74 fax nvarchar 50 yes yes yes yes
    75 aging30 money yes no yes no
    76 statecode state no no yes no
    77 aging60 money yes no yes no
    78 statuscode status yes yes yes yes
    79 aging90 money yes no yes no
    89 address1_addressid primarykey yes yes yes yes
    90 address1_addresstypecode picklist yes yes yes yes
    91 address1_name nvarchar 200 yes yes yes yes
    92 address1_primarycontactname nvarchar 150 yes yes yes yes
    93 address1_line1 nvarchar 50 yes yes yes yes
    94 address1_line2 nvarchar 50 yes yes yes yes
    95 address1_line3 nvarchar 50 yes yes yes yes
    96 address1_city nvarchar 50 yes yes yes yes
    97 address1_stateorprovince nvarchar 50 yes yes yes yes
    98 address1_county nvarchar 50 yes yes yes yes
    99 address1_country nvarchar 50 yes yes yes yes
    100 address1_postofficebox nvarchar 20 yes yes yes yes
    101 address1_postalcode nvarchar 20 yes yes yes yes
    102 address1_utcoffset timezone yes yes yes yes
    103 address1_freighttermscode picklist yes yes yes yes
    104 address1_upszone nvarchar 4 yes yes yes yes
    105 address1_latitude float yes yes yes yes
    106 address1_telephone1 nvarchar 50 yes yes yes yes
    107 address1_longitude float yes yes yes yes
    108 address1_shippingmethodcode picklist yes yes yes yes
    109 address1_telephone2 nvarchar 50 yes yes yes yes
    110 address1_telephone3 nvarchar 50 yes yes yes yes
    111 address1_fax nvarchar 50 yes yes yes yes
    112 address2_addressid primarykey yes yes yes yes
    113 address2_addresstypecode picklist yes yes yes yes
    114 address2_name nvarchar 200 yes yes yes yes
    115 address2_primarycontactname nvarchar 150 yes yes yes yes
    116 address2_line1 nvarchar 50 yes yes yes yes
    117 address2_line2 nvarchar 50 yes yes yes yes
    118 address2_line3 nvarchar 50 yes yes yes yes
    119 address2_city nvarchar 50 yes yes yes yes
    120 address2_stateorprovince nvarchar 50 yes yes yes yes
    121 address2_county nvarchar 50 yes yes yes yes
    122 address2_country nvarchar 50 yes yes yes yes
    123 address2_postofficebox nvarchar 20 yes yes yes yes
    124 address2_postalcode nvarchar 20 yes yes yes yes
    125 address2_utcoffset timezone yes yes yes yes
    126 address2_freighttermscode picklist yes yes yes yes
    127 address2_upszone nvarchar 4 yes yes yes yes
    128 address2_latitude float yes yes yes yes
    129 address2_telephone1 nvarchar 50 yes yes yes yes
    130 address2_longitude float yes yes yes yes
    131 address2_shippingmethodcode picklist yes yes yes yes
    132 address2_telephone2 nvarchar 50 yes yes yes yes
    133 address2_telephone3 nvarchar 50 yes yes yes yes
    134 address2_fax nvarchar 50 yes yes yes yes
    139 ownerid
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Right you are! Well, it smells like poor design to me. Probably someone thought it would save on disk accesses to store all the addresses in the same row. But what a nightmare it must be to use! Especially since each address has 24 columns!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Copious quantities of recreational pharmaceuticals! Who "blessed" that for realease ?!?!

    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "(isn't a trigger more load on the database than a calculation on a view?)"

    Yes and no. Remember that the view recalculates the result every time it executes, while the trigger only calculates when the value changes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •