design

[beginner]

I am designing a database. now i have some questions:

the requirement is as below:

each user can have multiple type of phone, he(she) can name it as whatever he(she) want,

for example
user1 have two phone, one is sch3590, another is Motorola6000.
he named the first one as smallphone, named the second one as MotorPhone.
i created table user_phone, primary key is username and devicename.
(username,devicename,devicetype)
user1, samllphone,sch3590
user1, MotorPhone,Motorola6000.


we send the service to user by different method, we don't care about the detail.
so we can say method1, method2, method3 ...
some service can only support some of method.
so i create table service_method. primary key is servicename,methodname.
(servicename,methodname)
service1 method1
service1 method2
service2 method3
service2 method3
service3 method4
...

also some device can only support some of method.
so i created table device_method to record it.
(devicetype, methodname)
sch3590 method1
sch3590 method4
Motorola6000 method1
Motorola6000 method2
Motorola6000 method3
Motorola6000 method4

then user can select service if his device can support it.
how to define this user's selection table?
i define it as
(user,phonename,servicename,method).
user1,smallphone,service1,method1
user1,smallphone,service3,method4
user1,Motorphone,service1,method1
user1,Motorphone,service2,method3

but i can't prevent user from inserting value like:
user1,smallphone,service2,method3.
as you can see, user1's smallphone is sch3590,
it only support method1 and method4. it don't support method3.

or i can define it as
(user,phonename,phonetype,servicename,method).
user1,smallphone,sch3590,service1,method1
user1,smallphone,sch3590,service3,method4
user1,Motorphone,motorola6000,service1,method1
user1,Motorphone,motorola6000,service2,method3
then i can create a foreign key between this table and device_method table.
but as you can see, this table is actually redundant.

[sambavan]

I think its being answered on the administration forum. So, I'm closing this thread.

http://www.dbasupport.com/forums/sho...threadid=13582

Sam