MySQL Database design - relations table

I have got a website where users can post photo's, video's, links, articles and events.

But now comes the hard part (for me): I want the users to be able to attach photo's, videos and links to events and/or articles.

What is best practice for this? Should i create another table where i link them to each other:

[media2articles]
m_id, m_type, media_id, m_开发者_开发技巧article_id

[media2events]
m_id, m_type, media_id, m_events_id

or should i add some fields to my current tables like this:

[videos]
v_id, v_file, **v_article_id, v_event_id**

Or is there any other way ?

It depends on the type of relationship your data has. Also keep in mind that you want to think about the relationship it'll have in the future because changing the schema can be painful.

• If a photo is always of a single event, never more than one, never less than one, then you'd put your event id in your photos table.
• If an event always has one photo, never more, and a photo can be of multiple events, then you'd put the photo id in your event table.
• If an event can have more than one photo, and a photo can be of more than one event, then you create a third table.
• If each event has one photo and each photo is of one event, then you might be engaging in attribute splitting, and maybe the two tables should really be one. (I'd guess not.)

This is basic relational modeling; reading up on it will greatly aid you in database design.

Generally you'd use a "link" table, with "to_" and "from_" references (or whatever is appropriate to your data model). That will allow a photo to be linked to more than one article, or vice-versa.

If one media item might be associated with different events, and different events might share the same media item, then you'd need an additional table to decompose the N:M relation between the 2 current tables.

I would create the "media" tables for this and link them. Adding columns to the existing table is a terrible design. You won't be able to support 1:n relationships unless you continue adding more columns to the table.