By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

creation of table that type of format........

P: n/a
hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
------------------------------------------
empid ph
----- ---------------------------------
office Mobile home
--------- -------- --------
100 9380768532 98455555 98822213

--------------------------------------------

i want above type of format and then how to insert into values that
phone table . please help me.

Apr 8 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
surya (su*******@gmail.com) writes:
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
------------------------------------------
empid ph
----- ---------------------------------
office Mobile home
--------- -------- --------
100 9380768532 98455555 98822213

--------------------------------------------

i want above type of format and then how to insert into values that
phone table . please help me.


The INSERT statement would be:

INSERT phonetable (empid, office, mobile, home)
VALUES (100, '9380768532', '98455555', '98822213')

If you are using some application environment, it is not unlikely that
the Client API offers some interface that constructs the INSERT statement
under the covers, but gives you a "nicer" interface.

The table design as such is not unquestionable. You have indicated
that office, modbile and home are infact subfields of ph, but there
is no such thing in a database table.

Depending on your requirements, it may be better to do:

CREATE TABLE phonetypes
(phonetype char(3) NOT NULL,
phonetypename varchar(20) NOT NULL,
CONSTRAINT pk_phonetypes PRIMARY KEY(phonetypeid))

INSERT phonetypes (phonetypes, phonetypename)
VALUES ('OFC', 'Office')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('MOB', 'Mobile')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('HOME', 'Home')

CREATE TABLE phonenumbers
(empid int NOT NULL,
phoneno varchar(200) NOT NULL,
phonetype char(3) NOT NULL,
isdefault bit NOT NULL,
CONSTRAINT pk_phonenumbers PRIMARY KEY(empid, phoneno),
CONSTRAINT fk_phonetype FORIEGN KEY (phonetype)
REFERENCES phonetypes (phonetype),
CONSTRAINT fk_employees (empid)
REFERENCES employees(empid))

There would be a trigger on phonenumbers, that enforces that isdefault may
be 1 for at most one combination of (empid, phoneno).

This design buys you more flexibility. Some people have more than one mobile
phone. It also permits you to add other telephone types such as FAX or
IP telephone like Skype. Since on services like Skype you don't have
traditional telephone numbers, but, as I understand it, something that looks
more like an email address, I've made phoneno varchar(200).
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 8 '06 #2

P: n/a
"surya" <su*******@gmail.com> wrote in news:1144477944.400250.34550
@z34g2000cwc.googlegroups.com:
hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Aren't you going to fail your class if you don't
do your own homework?

Apr 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.