MySQL database design problem

Question

I have design problem, which I would love to get a hand with. I normally don't have problems with designing databases, but this one gives me some trouble. :) I'm making a program which is basically a task-manager, where you can create Activities and Events. An Activity may or may not have an Event associated with it, and an Event will have one to many associations. How do I design the Event database? Since every Event can have more than one association, will I need to make a table for each Event? Where the tablename is it's unique name? I'm completely blank. Thanks

Explain better: you have Activities objects that can have zero, one or more Events objects. Events are related only with activities or with something else? — Marco
– Marco, Commented Sep 21, 2011 at 15:47

egrunin · Accepted Answer · 2011-09-21 15:52:32Z

5

Since every Event can have more than one association, will I need to make a table for each Event?

No. You need some version of:

Activity
--------
ActivityID
ActivityDescription

Event
-----
EventID
EventDescription

EventActivity
-------------
EventID
ActivityID

Then, to list an Event with all its Activities:

SELECT * from Event
LEFT OUTER JOIN EventActivity ON Event.EventID = EventActivity.EventID
LEFT OUTER JOIN JOIN Activity ON Activity.ActivityID = EventActivity.ActivityID

You need the LEFT OUTER JOIN instead of the usual INNER JOIN in case there are no Activities for the event.

edited Sep 21, 2011 at 15:52

answered Sep 21, 2011 at 15:47

egrunin

25.2k8 gold badges53 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marco Over a year ago

@egrunin: using your answer (+1 for you) I give OP a query to catch all items... I hope you agree :)

Martin Over a year ago

Looks like what I need. :) To clarify something: I need an input in EventActivity for each Activity in there? So if I had an Event with ID 1, and two activities which were associated with that event, with ID 1 and 2, I would have two lines in that database saying: "1, 1" and "1, 2"? :)

egrunin Over a year ago

Yes, you add one row to EventActivity when you want to connect an Activity with an Event.

Blindy · Accepted Answer · 2011-09-21 15:50:13Z

1

Well starting with the obvious:

Event(evid PK, event details)
Activity(actid PK, activity details)
EventActivityRelationship(evid FK, actid FK)  -- both make the primary key together

-- where PK is primary key and FK is foreign key

Then to make a link between an event and an activity, all you need to do is to add the two ID's together to the relationship table.

And keep in mind, relational databases (basically anything that's not NoSQL) grow downward (they gain rows), never horizontally (never in columns or tables) as you add more data. That will help you make intuitive database designs.

answered Sep 21, 2011 at 15:50

Blindy

68k10 gold badges96 silver badges141 bronze badges

1 Comment

Martin Over a year ago

Thanks a lot for the easy-to-understand answer. :) As I'm far from an expert in this, it has become really helpful.

Marco · Accepted Answer · 2011-09-21 15:50:10Z

0

Using @egrunin answer, if you need to get all activities and events you can use:

SELECT a.ActivityID, e.EventID
FROM Activity a
LEFT JOIN EventActivity ea
ON a.ActivityID = ea.ActivityID
LEFT JOIN Event e
ON ea.EventID = e.EventID

answered Sep 21, 2011 at 15:50

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Collectives™ on Stack Overflow

MySQL database design problem

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related