Hello to all you good people - I'm having a bit of trouble working out a suitable design for a system I am developing

Here's the general idea of what I want to model:

My system basically consists of users and events.

Each user can be linked to an event in 2 ways - either by having subscibed to that event, or having created it.

The way I have modeled this is as follows:

I have an events table - containing a unique event ID
I have a user table - containing a unique user ID
I have a user_event table- containing a unique user_event ID and event id/user id as 2 foreign keys

I have a user_event_creation table - with a unique ID and a user_event_id as a foreign key

I have a user_event_subscription table - with a unique ID and user_event_id id as a foreign key

Does this seem like the correct way to have designed the database? I am developing the system in ruby on rails by the way!

Any help is appreciated!

Cheers,

Michael

Got any DFD's you can upload - having a tired morning, struggling to visualise this atm.
I think I get the problem and the first 3 tables appear to be correct.

Sorry I don't actually, I'll knock one up and post it - thanks for the swift reply though!

OK -it isn't so much a DFD as an outline of how my tables shall look.

as you said - I am quite sure the link between users and events is sound, but I'm not so sure how to build a method of determing whether that link is subscription or creation based. I have attached the current database design which outlines how I have currently built the database.

If you only want to show if the link is subscription or creation based then how about adding a yes/no field to your userevents table?
I.e. User_Events(ID, Users_Id, Events_Id, IsThisASubscription)

I assume an event can have more than one subscriber. Can an event have more than one creator?

nope, it can only have one creator

I shall try this method thanks - although I think I might have tried it before and there was a reason why I didnt implement it!

Thanks for your help though!

Then you do not need a separate table for storing event creators. The creator is an attribute of event, and should be stored in the event table.

Users (UserID, UserDetails...)
Events (EventID, UserID, EventDetails...)
Subscribers (UserID, EventID, SubscriptionDetails...)

you. are. a. genious.


Thanks a lot!!

Yes. I. Know.
You. Are. Welcome.