Help with phoneBook db design

[E B via SQLMonster.com]

Hi All!!

I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas ?????

TNX

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

[Tony Rogerson]

How far have you got so far? Can you post your table designs.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"E B via SQLMonster.com" <u6539@uwe> wrote in message
news:58b7defdfb401@uwe...

Hi All!!

I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas ?????

TNX

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

[E B via SQLMonster.com]

so this is my question, i need some advice for implementation.

Tony Rogerson wrote:

How far have you got so far? Can you post your table designs.

Hi All!!

[quoted text clipped - 8 lines]

TNX

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

[David Portas]

E B via SQLMonster.com wrote:

so this is my question, i need some advice for implementation.

Tony Rogerson wrote:

How far have you got so far? Can you post your table designs.

Hi All!!

[quoted text clipped - 8 lines]

TNX

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

Designing a logical data model requires much more thorough analysis of
your requirements than we are likely to achieve through an online
discussion. You could take a look at the following link for some
inspiration. However, if you lack data modelling skills then I suggest
you study some fundamentals before you set to work. Designing a data
model is a science as well as an art. If you aren't familiar with the
principles then example solutions aren't going to be much help.

http://www.databaseanswers.org/data_models

--
David Portas
SQL Server MVP
--

[Tony Rogerson]

I'd certainly break the name up into first and last names, probably
nvarchar(50), also, you'll need other information to uniquely identify this
individual.

Phone number can be broken up into country code, area code and number as
well.

Do you need to think about extension number too?

I'd make the phone number (country code, area code, number) a primary key,
but use a surrogate key to foreign key between the individual and the phone
number.

So you will have 3 tables...

individual
other identifying cols...
first_name
last_name
id int not null identity constraint sk_individual unique nonclustered

phone_number
country_code
area_code
local_number
id int not null identity constraint sk_phone_number unique
nonclustered

individual_phone_number
individual_id int not null references individual( id ),
phone_number_id int not null references phone_number( id )

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"E B via SQLMonster.com" <u6539@uwe> wrote in message
news:58b8a9c7d1765@uwe...

so this is my question, i need some advice for implementation.

Tony Rogerson wrote:

How far have you got so far? Can you post your table designs.

Hi All!!

[quoted text clipped - 8 lines]

TNX

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

[E B via SQLMonster.com]

tnx, however i need to implement a distribution lists, e.g

idividual can have 3 phon numbers and 2 lists (inside this list there are
phone numbers and also possible inside this list there are another list)

TNX

Tony Rogerson wrote:

I'd certainly break the name up into first and last names, probably
nvarchar(50), also, you'll need other information to uniquely identify this
individual.

Phone number can be broken up into country code, area code and number as
well.

Do you need to think about extension number too?

I'd make the phone number (country code, area code, number) a primary key,
but use a surrogate key to foreign key between the individual and the phone
number.

So you will have 3 tables...

individual
other identifying cols...
first_name
last_name
id int not null identity constraint sk_individual unique nonclustered

phone_number
country_code
area_code
local_number
id int not null identity constraint sk_phone_number unique
nonclustered

individual_phone_number
individual_id int not null references individual( id ),
phone_number_id int not null references phone_number( id )

Tony.

so this is my question, i need some advice for implementation.

[quoted text clipped - 5 lines]

TNX

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

[E B via SQLMonster.com]

thanks David , i famaliar with this site, however The Tony post is pretty
good however i need also implemets lists (distribution lists) they can hold
phone numbers and also other lists and so on.
TNX

David Portas wrote:

so this is my question, i need some advice for implementation.

[quoted text clipped - 8 lines]

--
Imagination is more important then knowledge. (A.Einshtein)

Designing a logical data model requires much more thorough analysis of
your requirements than we are likely to achieve through an online
discussion. You could take a look at the following link for some
inspiration. However, if you lack data modelling skills then I suggest
you study some fundamentals before you set to work. Designing a data
model is a science as well as an art. If you aren't familiar with the
principles then example solutions aren't going to be much help.

http://www.databaseanswers.org/data_models

--
Imagination is more important then knowledge. (A.Einshtein)

Message posted via http://www.sqlmonster.com

[Doug]

you have people. first name, last name, address, and identity, i'll
call "peopleid."
you have a "phone number.". the actual number, and something i'll call
"phoneid."

you have a cross reference table of "assignedphone." in that table
will be AssignedID, peopleid, phoneid, adn perhaps startdates,
enddates, etc.

You will have a different cross reference table of "distribution."
DistributionID, and another peopleID for the "owner of the distribution
list." Here it gets a little interesting. If your distribution list is
a list of phone numbers, then include PhoneID's. If your distribution
list is a list of people, then include teh peopleID's. For the latter
case, use the assignedphone to go get the assigned phones, then off to
the phonenumbers to get the actual numbers.

Start there. When you have mastered the logic, then grow the app to
include the second order distribution lists. this is enough to get you
started though.