|
Home > Archive > Microsoft SQL Server forum > December 2005 > Help with phoneBook db design
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help with phoneBook db design
|
|
| E B via SQLMonster.com 2005-12-12, 7:23 am |
| 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.webservertalk.com
| |
| Tony Rogerson 2005-12-12, 7:23 am |
| 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 webservertalk.com" <u6539@uwe> wrote in message
news:58b7defdfb401@u
we...
> 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.webservertalk.com
| |
| E B via SQLMonster.com 2005-12-12, 7:23 am |
| so this is my question, i need some advice for implementation.
Tony Rogerson wrote:[color=darkred
]
>How far have you got so far? Can you post your table designs.
>
>[quoted text clipped - 8 lines]
--
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.webservertalk.com
| |
| David Portas 2005-12-12, 7:23 am |
| E B via webservertalk.com wrote:
> so this is my question, i need some advice for implementation.
>
>
>
> Tony Rogerson wrote:
>
> --
> Imagination is more important then knowledge. (A.Einshtein)
>
> Message posted via http://www.webservertalk.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 2005-12-12, 7:23 am |
| 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_num
ber
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 webservertalk.com" <u6539@uwe> wrote in message
news:58b8a9c7d1765@u
we...
> so this is my question, i need some advice for implementation.
>
>
>
> Tony Rogerson wrote:
>
> --
> Imagination is more important then knowledge. (A.Einshtein)
>
> Message posted via http://www.webservertalk.com
| |
| E B via SQLMonster.com 2005-12-12, 7:23 am |
| 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:[color=darkred
]
>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_num
ber
> individual_id int not null references individual( id ),
> phone_number_id int not null references phone_number( id )
>
>Tony.
>
>[quoted text clipped - 5 lines]
--
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.webservertalk.com
| |
| E B via SQLMonster.com 2005-12-12, 7:23 am |
| 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:
>[quoted text clipped - 8 lines]
>
>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.webservertalk.com
| |
|
| 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.
|
|
|
|
|