CODEWITHSUNDEEP
phpmysqldatabase
PinkFloyd
PinkFloyd

Reputation: 118

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 ?

Upvotes: 0

Views: 295

Answers (4)

derobert
derobert

Reputation: 51197

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.

Upvotes: 1

Icarus
Icarus

Reputation: 63970

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.

Upvotes: 0

symcbean
symcbean

Reputation: 48387

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.

Upvotes: 0

Pointy
Pointy

Reputation: 414006

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.

Upvotes: 0

Related Questions