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?)

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

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

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!

Copious quantities of recreational pharmaceuticals! Who "blessed" that for realease ?!?!

-PatP

"(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.