What's the best way to manage file/item relationship?

Question

I'm working on a CMS basically because I want to learn how to build one from scratch, and am at the point where I have to stop and think about how I want to manage the relationship between files and let's say, for the sake of simplicity, blog entries.

If each file is going to be associated with one and only one "blog entry" (or item), then would the best way to store that data be to have a database table for the files with a column that stores the specific item id it is associated with?

I know that this is a pretty simple example, but file management is something completely new to me and I am at a bit of a standstill as to what to do next. Any suggestions would be hugely appreciated.

Thanks in advance!!

Answer 1

If each file is going to be associated with one and only one "blog entry" (or item), then would the best way to store that data be to have a database table for the files with a column that stores the specific item id it is associated with?

Yes, that's the way. It's called a one-to-many relationship, because each blog entry can be linked to many files, but each file is linked to only one blog entry.

Last but not least, don't forget to define a foreign key constraint between the files and blogs_entries tables. This is optional, but will help you guarantee referential integrity, which in other words will prevent you from having files that do not belong to a valid blog entry.