Need advice on structuring a database

[Dragonhunter]

Hello,

I want to build a database with two tables, Characters and Abilities.

So in the first table I would have:

# Character Abilities
0 Fred Resistance, shield, firebreathing
1 Bob Climbing, swordfighting
etc...

and in the second table I would have:

# Ability Description
0 Resistance This character can resist things
1 Swordfighting This character can swordfight
etc...

I can program in ASP but I do not understand how to structure things so
that I can have a list of abilities for each character. I was thinking
I could have a string like: "Resistance, shield, firebreathing" under a
column called "Abilities" in the record for Fred, and then parse the
list and retrieve the descriptions from the second database. But this
seems like 'not the way someone that knew what they were doing would do
it'. So, is there a better way?

[Manohar Kamath [MVP]]

I would recommend three tables, as follows:


AbilityID AbilityName AbilityDesc

CharacterID CharacterName

CharacterID AbilityID

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
news:3F5FB47C.58702C07@yahoo.com...

> Hello,
>
> I want to build a database with two tables, Characters and Abilities.
>
> So in the first table I would have:
>
> # Character Abilities
> 0 Fred Resistance, shield, firebreathing
> 1 Bob Climbing, swordfighting
> etc...
>
> and in the second table I would have:
>
> # Ability Description
> 0 Resistance This character can resist things
> 1 Swordfighting This character can swordfight
> etc...
>
> I can program in ASP but I do not understand how to structure things so
> that I can have a list of abilities for each character. I was thinking
> I could have a string like: "Resistance, shield, firebreathing" under a
> column called "Abilities" in the record for Fred, and then parse the
> list and retrieve the descriptions from the second database. But this
> seems like 'not the way someone that knew what they were doing would do
> it'. So, is there a better way?
>
>

[GVaught]

To add to Manohar's suggestion, if you plan to use the whole name I would
split the CharacterName into something like FName, MI, LName. This will make
searching for data easier, if you need to search on a character's lastname
or firstname. Middle initial is there to distinguish between to Mary Smith's
or Joe Blow's; this field can be eliminated if the First Name and Last Name
doesn't play an important part in the overall data and you have another way
to distinguish between two Mary Smith's.

"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:O1uXBJCeDHA.696@TK2MSFTNGP09.phx.gbl...

> I would recommend three tables, as follows:
>
>
> AbilityID AbilityName AbilityDesc
>
> CharacterID CharacterName
>
> CharacterID AbilityID
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
> news:3F5FB47C.58702C07@yahoo.com...

> > Hello,
> >
> > I want to build a database with two tables, Characters and Abilities.
> >
> > So in the first table I would have:
> >
> > # Character Abilities
> > 0 Fred Resistance, shield, firebreathing
> > 1 Bob Climbing, swordfighting
> > etc...
> >
> > and in the second table I would have:
> >
> > # Ability Description
> > 0 Resistance This character can resist things
> > 1 Swordfighting This character can swordfight
> > etc...
> >
> > I can program in ASP but I do not understand how to structure things so
> > that I can have a list of abilities for each character. I was thinking
> > I could have a string like: "Resistance, shield, firebreathing" under a
> > column called "Abilities" in the record for Fred, and then parse the
> > list and retrieve the descriptions from the second database. But this
> > seems like 'not the way someone that knew what they were doing would do
> > it'. So, is there a better way?
> >
> >

>
>

[mark4asp]

On Wed, 10 Sep 2003 23:32:10 GMT, Dragonhunter
<dragonhunter97@yahoo.com> wrote:

>Hello,
>
>I want to build a database with two tables, Characters and Abilities.
>
>So in the first table I would have:
>
># Character Abilities
>0 Fred Resistance, shield, firebreathing
>1 Bob Climbing, swordfighting
>etc...
>
>and in the second table I would have:
>
># Ability Description
>0 Resistance This character can resist things
>1 Swordfighting This character can swordfight
>etc...
>
>I can program in ASP but I do not understand how to structure things so
>that I can have a list of abilities for each character. I was thinking
>I could have a string like: "Resistance, shield, firebreathing" under a
>column called "Abilities" in the record for Fred, and then parse the
>list and retrieve the descriptions from the second database. But this
>seems like 'not the way someone that knew what they were doing would do
>it'. So, is there a better way?

In case you didn't get that (from Manohar) it looks something like
this:

Abilities:

AbilityID AbilityName AbilityDesc
1 Resistance etc...
2 shield
3 firebreathing
4 Climbing
5 swordfighting

Characters:

CharacterID CharacterName
1 Fred
2 Bob

CharacterAbilities:

CharacterID AbilityID
1 1
1 2
1 3
2 4
2 5

Note: The ID numbers start at 1 rather than 0 because this is likely
to be the default value which identity and autonumber values start at.

The 'key' of CharacterAbilities table is a composite key (using both
fields).

[Dragonhunter]

Thanks Manohar, GVaught, and Mark4asp for the advice. Just so I understand
it in completely, could I ask, is the table of IDs only for speed purposes?
It seems you could create the list of people and abilities without using IDs
and have only two tables, but I'm sure there is a reason why you guys are
suggesting to do it with 3 tables and with IDs. If speed is the reason,
could I get a ballpark multiplier for how much of an effect this has?

Thanks guys!


mark4asp wrote:

> On Wed, 10 Sep 2003 23:32:10 GMT, Dragonhunter
> <dragonhunter97@yahoo.com> wrote:
>

> >Hello,
> >
> >I want to build a database with two tables, Characters and Abilities.
> >
> >So in the first table I would have:
> >
> ># Character Abilities
> >0 Fred Resistance, shield, firebreathing
> >1 Bob Climbing, swordfighting
> >etc...
> >
> >and in the second table I would have:
> >
> ># Ability Description
> >0 Resistance This character can resist things
> >1 Swordfighting This character can swordfight
> >etc...
> >
> >I can program in ASP but I do not understand how to structure things so
> >that I can have a list of abilities for each character. I was thinking
> >I could have a string like: "Resistance, shield, firebreathing" under a
> >column called "Abilities" in the record for Fred, and then parse the
> >list and retrieve the descriptions from the second database. But this
> >seems like 'not the way someone that knew what they were doing would do
> >it'. So, is there a better way?

>
> In case you didn't get that (from Manohar) it looks something like
> this:
>
> Abilities:
>
> AbilityID AbilityName AbilityDesc
> 1 Resistance etc...
> 2 shield
> 3 firebreathing
> 4 Climbing
> 5 swordfighting
>
> Characters:
>
> CharacterID CharacterName
> 1 Fred
> 2 Bob
>
> CharacterAbilities:
>
> CharacterID AbilityID
> 1 1
> 1 2
> 1 3
> 2 4
> 2 5
>
> Note: The ID numbers start at 1 rather than 0 because this is likely
> to be the default value which identity and autonumber values start at.
>
> The 'key' of CharacterAbilities table is a composite key (using both
> fields).

[Manohar Kamath [MVP]]

Not as much speed as non-redundancy -- called Normalization.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
news:3F611D7F.4352BB81@yahoo.com...

> Thanks Manohar, GVaught, and Mark4asp for the advice. Just so I

understand

> it in completely, could I ask, is the table of IDs only for speed

purposes?

> It seems you could create the list of people and abilities without using

IDs

> and have only two tables, but I'm sure there is a reason why you guys are
> suggesting to do it with 3 tables and with IDs. If speed is the reason,
> could I get a ballpark multiplier for how much of an effect this has?
>
> Thanks guys!
>
>
> mark4asp wrote:
>

> > On Wed, 10 Sep 2003 23:32:10 GMT, Dragonhunter
> > <dragonhunter97@yahoo.com> wrote:
> >

> > >Hello,
> > >
> > >I want to build a database with two tables, Characters and Abilities.
> > >
> > >So in the first table I would have:
> > >
> > ># Character Abilities
> > >0 Fred Resistance, shield, firebreathing
> > >1 Bob Climbing, swordfighting
> > >etc...
> > >
> > >and in the second table I would have:
> > >
> > ># Ability Description
> > >0 Resistance This character can resist things
> > >1 Swordfighting This character can swordfight
> > >etc...
> > >
> > >I can program in ASP but I do not understand how to structure things so
> > >that I can have a list of abilities for each character. I was thinking
> > >I could have a string like: "Resistance, shield, firebreathing" under a
> > >column called "Abilities" in the record for Fred, and then parse the
> > >list and retrieve the descriptions from the second database. But this
> > >seems like 'not the way someone that knew what they were doing would do
> > >it'. So, is there a better way?

> >
> > In case you didn't get that (from Manohar) it looks something like
> > this:
> >
> > Abilities:
> >
> > AbilityID AbilityName AbilityDesc
> > 1 Resistance etc...
> > 2 shield
> > 3 firebreathing
> > 4 Climbing
> > 5 swordfighting
> >
> > Characters:
> >
> > CharacterID CharacterName
> > 1 Fred
> > 2 Bob
> >
> > CharacterAbilities:
> >
> > CharacterID AbilityID
> > 1 1
> > 1 2
> > 1 3
> > 2 4
> > 2 5
> >
> > Note: The ID numbers start at 1 rather than 0 because this is likely
> > to be the default value which identity and autonumber values start at.
> >
> > The 'key' of CharacterAbilities table is a composite key (using both
> > fields).

>

[Turkbear]

On Thu, 11 Sep 2003 23:02:40 -0500, "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote:

>Not as much speed as non-redundancy -- called Normalization.

You could have only 2 tables if there were a finite number of abilities 1 character could have;you could then have:

Person_table

Person_Id
Ability_1_id
Ability_2_id
Ability_3_id
Person_name
etc...


Ability_table:

Ability_Id
Ability_name
etc..

The advantage to the multiple table setup is the ability to modify the characteristics or attributes of an Ability
without having to recode every character that has that ability -

For the type of application you are constructing, understanding normalization will save you much time and frustation.

Have fun...

[Phillip Windell]

....and use checkboxes for the abilities.


--

Phillip Windell [CCNA, MVP, MCP]
[email]pwindell@wandtv.com[/email]
WAND-TV (ABC Affiliate)
[url]www.wandtv.com[/url]

"GVaught" <glvaught@hotmail.com> wrote in message
news:eoaGPaJeDHA.2268@TK2MSFTNGP10.phx.gbl...

> To add to Manohar's suggestion, if you plan to use the whole name I

would

> split the CharacterName into something like FName, MI, LName. This

will make

> searching for data easier, if you need to search on a character's

lastname

> or firstname. Middle initial is there to distinguish between to Mary

Smith's

> or Joe Blow's; this field can be eliminated if the First Name and

Last Name

> doesn't play an important part in the overall data and you have

another way

> to distinguish between two Mary Smith's.
>
> "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in

message

> news:O1uXBJCeDHA.696@TK2MSFTNGP09.phx.gbl...

> > I would recommend three tables, as follows:
> >
> >
> > AbilityID AbilityName AbilityDesc
> >
> > CharacterID CharacterName
> >
> > CharacterID AbilityID
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > [url]www.dotnetbooks.com[/url]
> >
> >
> > "Dragonhunter" <dragonhunter97@yahoo.com> wrote in message
> > news:3F5FB47C.58702C07@yahoo.com...

> > > Hello,
> > >
> > > I want to build a database with two tables, Characters and

Abilities.

> > >
> > > So in the first table I would have:
> > >
> > > # Character Abilities
> > > 0 Fred Resistance, shield, firebreathing
> > > 1 Bob Climbing, swordfighting
> > > etc...
> > >
> > > and in the second table I would have:
> > >
> > > # Ability Description
> > > 0 Resistance This character can resist things
> > > 1 Swordfighting This character can swordfight
> > > etc...
> > >
> > > I can program in ASP but I do not understand how to structure

things so

> > > that I can have a list of abilities for each character. I was

thinking

> > > I could have a string like: "Resistance, shield, firebreathing"

under a

> > > column called "Abilities" in the record for Fred, and then parse

the

> > > list and retrieve the descriptions from the second database.

But this

> > > seems like 'not the way someone that knew what they were doing

would do

> > > it'. So, is there a better way?
> > >
> > >

> >
> >

>
>