Database design - doubt in mapping the relationships.

Question

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?

Answer 1

Why not a many-many between SUBNS_DEVICES and USER_SUBNS?

Answer 2

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?

Answer 3

Are users limited to choosing Subscription/Device pairs SUBNS_DEVICES? If so, why no reference from USER_SUBNS to SUBNS_DEVICES?