[aklo]

Hi guys i'm designing a database and I chanced upon something that I'm not sure what to call it.

3 Tables:

User
SMS
UserSMS

using arrow head as "Many" and single line as "One"


1 user can send many sms
1 sms can only belong to 1 user / This sms is unique to this particular user

I'm thinking that the UserSMS table is using a composite key but the 1:1 relationship confused me.

Example composite key (1st number user_id, 2nd number is sms_id)
(user_id, sms_id)
(1,1) <==unique
(1,2) <==unique
(2,1) <==unique but wrong because sms ID is 1:1 relationship

I might even say that the sms id is a primary key of UserSMS table

Anyway is there a name for this type of things? Or is it even correct?

This post has been edited by aklo: 11 May 2012 - 08:21 PM

[Atli]

Hey.

In that situation you are using the wrong type of relationship. You are using a "Many-To-Many" relationship (N:M), while what you should be using is a "One-To-Many" relationship (1:N).

Essentially, you should not be using the third link table. The ID of the User that owns the SMS should be put into the SMS table as a foreign key. The link table is only necessary if you intend to allow each SMS to belong to multiple Users.



Your other option, if for some reason you need to keep the third table, would be to put a unique constraint on the SMS_Id in the link table. A composite key with the User_Id column doesn't work, for the reason you detailed; it only restricts the SMS_Id so that it is unique per user, not unique overall. - The relationship between the SMS.SMS_Id and the UserSMS.SMS_Id fields would have to be 1:1, rather than 1:N as is the norm with N:M link table relationships.

[aklo]

Thanks for the reply.

Your model is definitely the correct way.

Upon closer reading of your last part of your comments, my design should also work but in a weird and non conventional way that also needs extra table.

I'll stick with 2 tables.

[BobRodes]

There are reasons for having a 1:1 table relationship, however. Generally, this would be if only some of the fields in a table were in relationship to some other table, and the rest weren't. You might in this case break the table in two, especially if the relationship to the other table were occasional, and therefore would imply a lot of null data values.