MySQL Database design - relational modeling

[eluser]Oozooo[/eluser]
Hi there,

I just can't get my head around this.

I have got a (codeigniter) website where users can post images, articles, videos, events, links (each has its own table).

What i want is:
When a user is writing an article or adding an event, allow them to add images, links, or videos that are attached to that particular event or article.

My problem:
What is the best database design for this ?

Do i really need to add 6 tables like this ?

Code:

[images2articles]
id, image_id, article_id

[images2events]
id, images_id, event_id

[links2articles]
id, link_id, article_id

etc etc

Some thoughts on this ?

[eluser]WanWizard[/eluser]
Basically, if you have a one-to-many relationship, you can create the relationship by using the key of the 'one' table as foreign key in the 'many' table. If you have a many-to-many relationship, you need a relationship or junction table, that contains the id's of the two tables as foreign keys. Giving that table a unique key as well is optional, as you would normally never query that table.

Sometimes it helps to use a tool to define the tables and the relationships visually. Some use MS Access (which I hate!), or MySQL workbench.

[eluser]Oozooo[/eluser]
Ok, so let me get this right:

You're telling me to add 2 columns to the images, links, and video tables like this:

Code:

[images]
id, file, title, article_id, event_id

something is telling me this isn't the way to go.

[eluser]WanWizard[/eluser]
No, I'm saying that if you have a one-to-many relation, that is what you should do.

In this case, if an image can be linked to multiple articles, and one article can have multiple images attached to it, you have a many-to-many relation, and you need a junction table joining the two. Same for events.

That's why I said that it is sometimes easier to visualize your structure (draw an ERD)...

[eluser]tonanbarbarian[/eluser]
what you need to ask is... will an image be alowed to be linked to multiple articles?
and the answer to this also comes down to how you want the site to work

if when the user is creating the article you allow them to upload one or more images, and those images only link to the article and cannot be linked to other aticles, you have a one-to-many relationship

If you have a place on the site where the user can upload images, and then when they are creating an article they can select from a list of images and assign one or more to the article, then you have a many-to-many relationship


so if you have a one-to-many relationship you have have 2 tables
articles:
id
name
body
... etc

images:
id
article_id
filename
...etc

As you can see the article_id in the images table indicates which article this image always belongs. The image can belong to only one article, but the article can then have multiple images, thus a one-to-many relationship

If you have a many to many relationship you need 3 tables
articles:
id
name
body
...etc

images:
id
filename
...etc

articles_images
article_id
image_id

So this time the images table does not have an article_id and instead we have the join table called articles_images which links the 2 tables via the article_id and the image_id.
This allows each article to have many images and each image to belong to mutliple articles, thus a many-to-many relationship

You can add a unique id primary key to the articles_images table if you want, but it is not necessary if you make both the article_id and image_id part of the primary key.

Sometimes when you are allowing the user from the website to select one article_image record and edit or delete it, it can be easier to work with if you have a unique id for the row in question, because you only have to pass a single id, rather than 2 id fields to identifiy the record you are processing.
As a result something I use an id field which is the primary key, and other times I do not. It depends on the processing required by the site

[eluser]Unknown[/eluser]
Ok, you can have a master content page were you put everything. Like a content pool. One field stating if it's a html bloc or image or whatever. And one table saying that content X has these components (and a field that can have a serialized array for instance).

I guess it's a personal choice. And a db design issue. Wordpress follow a bit like this. posts, comments and pages are all at the same table and they have a table for media (images, videos and all related). And then the code will construct itself.

I have a approach to this using mysql for my tags and defining content (for the main site search) and a REST database (I use MongoDB) where the content actually is. But I have LOADS of information non-searchable. I have a feature like facebook wall and I store it on Mongo.

But this is data design. You need a pencil and some scrap paper and draw a lot of solutions. one of it will be the perfect one. Believe me: nothing beats paper and pencil when designing a database.