Database design - doubt in mapping the relationships
I am having confusion in designing the database.
Its a Subscription based application :
One Subscription can have multiple display devices.
While Subscribing, the user will be asked to select one of the displayed devices.
Keeping this in mind, I've come up like this:
Is the above approach correct?
Should the USER_SUBNS.DISP_DEV_CD(foreign key) refer to DISPLAY_DEVICES.DISP_DEV_CD or SUBNS_DEVICES.DEV_CD?
Not nearly enough information to answer your question.
Can a User have more than one Subscription?
Is a Display_Device table a list of actual devices like The iPhone in my hand, or just types?
If Display Devices are individual items that are owned by users then you have a 1 - many between users and devices.
If a user can have more than one subscription then you have a one to many with Users and Subscriptions. If each subscription can I have more than one display and each display can be in more than one subscription then you have a many to many between displays and subscriptions.
_not to your question but...
And why are you using varchars for your PKs?
Why not a many-many between SUBNS_DEVICES and USER_SUBNS?
Are users limited to choosing Subscription/Device pairs SUBNS_DEVICES? If so, why no reference from USER_SUBNS to SUBNS_DEVICES?