Normalizing help and naming conventions

I have an Access Database that I am working on normalizing to SQL Server.
This Access database has been revised multiple times, each time the
programmer (not me!) didn't work to get the existing data to the same level
as the revised program. For instance, the HairColor column has the
following DISTINCT values,
Auburn
Black
Black/Gray
Blonde
Brown
Brown/Gray
Dark Brown
Gray
Gray/Black
Gray/Brown
Lt. Brown
Red
Red-blonde
Red/Blonde
Red/Brown
Now the current revision of the Access database limits the values for the
HairColor column to,
Black
Blonde
Brown
Gray
Red
So I need to make the current values reflect the current dropdown. I know
how to go about this, but have a question about normalizing this database.
Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of just
leaving the text in there?
Also, if I am to create a new table, how does the naming convention work
with that? Should I prefix my table name with fk_HairColor, or something
like that?
Any advice is appreciated!
Thanks!
drewI would create a table named HairColor with the columns:
HairColorID int
HairColor varchar (20)
In any table that references this table, you can create a foreign key. You
will take up less space in your DB overall. Also, consider what would
happen if you decided to change "Lt. Brown" to "Light Brown". With the
normalized version, you change it in only one place.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
I have an Access Database that I am working on normalizing to SQL Server.
This Access database has been revised multiple times, each time the
programmer (not me!) didn't work to get the existing data to the same level
as the revised program. For instance, the HairColor column has the
following DISTINCT values,
Auburn
Black
Black/Gray
Blonde
Brown
Brown/Gray
Dark Brown
Gray
Gray/Black
Gray/Brown
Lt. Brown
Red
Red-blonde
Red/Blonde
Red/Brown
Now the current revision of the Access database limits the values for the
HairColor column to,
Black
Blonde
Brown
Gray
Red
So I need to make the current values reflect the current dropdown. I know
how to go about this, but have a question about normalizing this database.
Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of just
leaving the text in there?
Also, if I am to create a new table, how does the naming convention work
with that? Should I prefix my table name with fk_HairColor, or something
like that?
Any advice is appreciated!
Thanks!
drew|||Thanks for the response... I thought it was better to replace it with a FK,
but am trying to convince my boss the same...
So you don't use a different naming convention with FKs? I just have about
30 tables on this database and it is getting harder to read for each FK
table I include...
Thanks,
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>I would create a table named HairColor with the columns:
> HairColorID int
> HairColor varchar (20)
> In any table that references this table, you can create a foreign key.
> You
> will take up less space in your DB overall. Also, consider what would
> happen if you decided to change "Lt. Brown" to "Light Brown". With the
> normalized version, you change it in only one place.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
> I have an Access Database that I am working on normalizing to SQL Server.
> This Access database has been revised multiple times, each time the
> programmer (not me!) didn't work to get the existing data to the same
> level
> as the revised program. For instance, the HairColor column has the
> following DISTINCT values,
> Auburn
> Black
> Black/Gray
> Blonde
> Brown
> Brown/Gray
> Dark Brown
> Gray
> Gray/Black
> Gray/Brown
> Lt. Brown
> Red
> Red-blonde
> Red/Blonde
> Red/Brown
> Now the current revision of the Access database limits the values for the
> HairColor column to,
> Black
> Blonde
> Brown
> Gray
> Red
> So I need to make the current values reflect the current dropdown. I know
> how to go about this, but have a question about normalizing this database.
> Should I create a seperate table, HairColor and then assign ID's to the
> HairColor column? What is the benefit of doing it this way, instead of
> just
> leaving the text in there?
> Also, if I am to create a new table, how does the naming convention work
> with that? Should I prefix my table name with fk_HairColor, or something
> like that?
> Any advice is appreciated!
> Thanks!
> drew
>|||I did say to use a foreign key. Here's an example. Let's say that you have
a Persons table, and each person has a hair colour. Here's how the Persons
table would go:
create table Persons
(
PersonID int primary key
, HairColorID int not null
FOREIGN KEY FK1_Persons
REFERENCES HairColor
, ...
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
news:uW2Bs7LXGHA.196@.TK2MSFTNGP04.phx.gbl...
Thanks for the response... I thought it was better to replace it with a FK,
but am trying to convince my boss the same...
So you don't use a different naming convention with FKs? I just have about
30 tables on this database and it is getting harder to read for each FK
table I include...
Thanks,
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>I would create a table named HairColor with the columns:
> HairColorID int
> HairColor varchar (20)
> In any table that references this table, you can create a foreign key.
> You
> will take up less space in your DB overall. Also, consider what would
> happen if you decided to change "Lt. Brown" to "Light Brown". With the
> normalized version, you change it in only one place.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:eh9TXyLXGHA.5024@.TK2MSFTNGP04.phx.gbl...
> I have an Access Database that I am working on normalizing to SQL Server.
> This Access database has been revised multiple times, each time the
> programmer (not me!) didn't work to get the existing data to the same
> level
> as the revised program. For instance, the HairColor column has the
> following DISTINCT values,
> Auburn
> Black
> Black/Gray
> Blonde
> Brown
> Brown/Gray
> Dark Brown
> Gray
> Gray/Black
> Gray/Brown
> Lt. Brown
> Red
> Red-blonde
> Red/Blonde
> Red/Brown
> Now the current revision of the Access database limits the values for the
> HairColor column to,
> Black
> Blonde
> Brown
> Gray
> Red
> So I need to make the current values reflect the current dropdown. I know
> how to go about this, but have a question about normalizing this database.
> Should I create a seperate table, HairColor and then assign ID's to the
> HairColor column? What is the benefit of doing it this way, instead of
> just
> leaving the text in there?
> Also, if I am to create a new table, how does the naming convention work
> with that? Should I prefix my table name with fk_HairColor, or something
> like that?
> Any advice is appreciated!
> Thanks!
> drew
>|||>> Should I create a seperate table, HairColor and then assign ID's to the
HairColor column? What is the benefit of doing it this way, instead of
just
leaving the text in there? <<
Let's start with basics:
1) Do your research! Is there an industry standard code for hair
colors? Use it
2) If not, then design an encoding that has a missing and/or misc code
value -- Hey, I am bald!
3) If you expect the values to change, the list to be long, or that you
willneed to add other information later, then put the encoding into a
separate table.
4) If you expect the values to be static, the list to be short or that
you will not need to add other information later, then put the encoding
into a CHECK() constraint. For example, the ISO sex codes are going to
stay the same for awhile, as are the two-letter state codes.
Get a copy of SQL PROGRAMMING STYLE. You never, never prefix a data
element name with thigns to tell you (1) Where it is used -- so no
table names (2) how it is used -- so no pk- or fk- crap (3) how it is
store, so no data types. Follow ISO-11179 rules and name a thing for
what it is, independent of its location or usage.
Do not blindly design an encoding as a numbered list. Think about
hierarchical ("Dewey Decimal") codes, abbreviation codes, etc. That
is also in SQL PROGRAMMING STYLE.|||I second getting SQL Programming Style. I read it in 2 ws. It is
any easy read.|||For the record, I was agreeing with you!
Drew
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYsy3EMXGHA.752@.TK2MSFTNGP02.phx.gbl...
>I did say to use a foreign key. Here's an example. Let's say that you
>have
> a Persons table, and each person has a hair colour. Here's how the
> Persons
> table would go:
> create table Persons
> (
> PersonID int primary key
> , HairColorID int not null
> FOREIGN KEY FK1_Persons
> REFERENCES HairColor
> , ...
> )
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Drew" <drewDOTlaingATswvtc.dmhmrsas.virginia.gov> wrote in message
> news:uW2Bs7LXGHA.196@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response... I thought it was better to replace it with a
> FK,
> but am trying to convince my boss the same...
> So you don't use a different naming convention with FKs? I just have
> about
> 30 tables on this database and it is getting harder to read for each FK
> table I include...
> Thanks,
> Drew
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O5jEK4LXGHA.3808@.TK2MSFTNGP04.phx.gbl...
>