Normalize this data

How would you normalize this data into a useable database?
Customer Name City Contact Address
Contact First Name Contact Phone Number Contact Fax Number
Billing Address Country Contact Email
Customer Number Contact Title User Name
Contact Last Name Shipping Address Password
Postal Code Contact Cell Phone User Role PrivilegePut it in a table?
<billsahiker@.yahoo.com> wrote in message
news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
> How would you normalize this data into a useable database?
> Customer Name City Contact Address
> Contact First Name Contact Phone Number Contact Fax Number
> Billing Address Country Contact Email
> Customer Number Contact Title User Name
> Contact Last Name Shipping Address Password
> Postal Code Contact Cell Phone User Role Privilege
>|||On Oct 20, 1:18 pm, "Jay" <s...@.nospam.org> wrote:
> Put it in a table?
> <billsahi...@.yahoo.com> wrote in message
> news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
>
> > How would you normalize this data into a useable database?
> > Customer Name City Contact Address
> > Contact First Name Contact Phone Number Contact Fax Number
> > Billing Address Country Contact Email
> > Customer Number Contact Title User Name
> > Contact Last Name Shipping Address Password
> > Postal Code Contact Cell Phone User Role Privilege- Hide quoted text -
> - Show quoted text -
Two or more tables, and indicate primary and foreign keys. This was a
test item and that is all the information given.|||<billsahiker@.yahoo.com> wrote in message
news:1192908678.033282.164490@.k35g2000prh.googlegroups.com...
> On Oct 20, 1:18 pm, "Jay" <s...@.nospam.org> wrote:
>> Put it in a table?
>> <billsahi...@.yahoo.com> wrote in message
>> news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
>>
>> > How would you normalize this data into a useable database?
>> > Customer Name City Contact Address
>> > Contact First Name Contact Phone Number Contact Fax Number
>> > Billing Address Country Contact Email
>> > Customer Number Contact Title User Name
>> > Contact Last Name Shipping Address Password
>> > Postal Code Contact Cell Phone User Role Privilege- Hide quoted text -
>> - Show quoted text -
> Two or more tables, and indicate primary and foreign keys. This was a
> test item and that is all the information given.
>
How about doing your own homework...
--
David Portas|||Why do you assume it is school work? It was a job application test I
took yesterday.
> How about doing your own homework...
> --
> David Portas- Hide quoted text -
> - Show quoted text -|||Ah, because you told the truth, I will help. And there is little difference
between an employment test and homework. You should have said so in the
first place.
Basically, it looks like they got together and looked for ways to screw
applicants up. They probably thought they were being cute mixing up and
eleminating necessary attributes too.
The first thing that jumps out is that you're storing people and addresses,
so that's two tables:
People is 1-to-many to Addresses (more on addresses later).
Second (and it's glaring): "Customer Name" vs. "Contact First Name" &
"Contact Last Name"
These columns should be: "Prefix", "First Name", "Middle Name",
"Last Name" & "Suffix".
(Mr. John Adam Smith, Sr)
Third Customers have "User Role Privilege", "User Name" and Password,
Contacts do not. Therefore, some people have attributes that others do not.
This suggests storing Customers and Contacts seperatly.
--
If you take the list of attributes they provided and arrange it, more things
become apparent:
Customer Number
User Name
Password
User Role Privilege
Customer Name
Billing Address
City
Postal Code
Country
Shipping Address
Contact Title
Contact First Name
Contact Last Name
Contact Address
Contact Phone Number
Contact Cell Phone
Contact Fax Number
Contact Email
Comments:
"Billing Address" and "Shipping Address" are clearly attributes of the
Customer. However, "City", "Postal Code" (Zip Code) & "Country" are vague.
Are they for Billing, Shipping, or Contact. In fact, you really need those
for each address. Same for attributes that appear in Contact, you should
have those for Customers too. In general, they really messed with the
attribute list.
Also, what "State" is the address in?
People (not including addresses):
There are two approaches here: store the "Contact" as attributes in
Customer, or create three tables: Customer, Contact & People, where "People"
is the parent table. You'll probably have to define your own PK for people
too and store it as a FK in Customer alone with the Customer Only info.
Addresses:
Create a seperate address table with all approporiate attributes. Then
you need a way to link people to addresses. You can do this by adding a
column to one of the people tables, or creating a many-to-many relationship
between people/customer/contact tables and addresses.
Since you have three distinct address types, you might want an AddressType
column in addresses constrained either by a lookup table, or a check
constraint. There are pluses and minuses for each. I prefer lookup tables,
but I'm a 3NF+ bigot.
Well, that covers the basics. Can you normalize the tables now?
<billsahiker@.yahoo.com> wrote in message
news:1192905303.503854.262450@.i13g2000prf.googlegroups.com...
> How would you normalize this data into a useable database?
> Customer Name City Contact Address
> Contact First Name Contact Phone Number Contact Fax Number
> Billing Address Country Contact Email
> Customer Number Contact Title User Name
> Contact Last Name Shipping Address Password
> Postal Code Contact Cell Phone User Role Privilege
>

normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisRProbably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisR|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> > Probably not. The phone number really is (in North America) 10 digits,
of
> > which the first three are the area code. Beyond N America, things
change
> > somewhat (I believe).
> >
> > That said, you can have a table of phone numbers where there is an area
> code
> > column and a number column, with a FK from the area code to a table of
> area
> > codes. This ensures that only known area codes are put into your phone
> > number table.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> >However, if you're only using them for display, they should definitely
> > be in the same column, IMO...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Just as an FYI:
> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and "something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8 or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they have the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mill people. I found that
partly amusing, and partly really really worrying...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> > I'd be careful about prefix. Where I work right now, I use 9+1+area
> > code+number. In another place, it was 4+area code+number. In yet
> another,
> > it was 8+area code+number. Of course, at home, it's 1+area code+number.
> >
> > Sometimes, internationalization can be a right PITA...
> >
> I think we're talking about different things in regards to prefix. I'm
> talking about:
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I think
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:
> Just as an FYI:
>> For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
>> I'd be careful about prefix. Where I work right now, I use 9+1+area
>> code+number. In another place, it was 4+area code+number. In yet
>> another,
>> it was 8+area code+number. Of course, at home, it's 1+area
>> code+number.
>> Sometimes, internationalization can be a right PITA...
>>
>> I think we're talking about different things in regards to
>> prefix. I'm talking about:
>> (XXX) YYY - ZZZZ
>> Where XXX is the area code and YYY is the prefix. For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix,
>> although the number of digits are variable, but the something can be
>> totally different depending on country -- there may be more levels
>> of hierarchy, e.g. I think in some Asian countries they have 4 or 5.
>> The local phone system you're on will determine the prefix you're
>> talking about, 9 + 1, 4 + , etc.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.sqljunkies.com/weblog/amachanic
>> --|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:elA9%239V0EHA.3408@.tk2msftngp13.phx.gbl...
> And for further info, we don't have any prefix or area code in Denmark. We
> used to have a 2 digit area code in the past, but for many years we've
just
> had an 8 digit phonenumber.
> >
> > In Sweden, we have no prefix. We have CountryCode (obviously),
> > AreaCode and "something". :-)
Thanks for the info guys!
Both are countries I haven't worked with yet. I had a bear of a time w/
Asia (mostly due to lack of quality data, but it's been a few years since
that project so maybe things have improved) -- I can't wait until I get to
cover Europe :-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--