CODEWITHSUNDEEP
phpmysqldatabasecakephp
Derp
Derp

Reputation: 109

Organizing a Database

In my application I have 2 tables in the DB (MySQL): Companies and News. Company has many News.

Company can have a set of pictures which will be displayed on the company "view page" alongside with all relative information about this company.

I've added a Photos table with next fields: id company_id filename

My question is: Now I also need to have pictures which will belong to News. I should add another table, which will be called for example Media or I should add additional field (type) to my Photos table, rename company_id to foreign_id and then filter results in PHP and build more complex queries with for example AND 'type' = 1 to fetch photos related to Company and 'type = 2' to fetch photos related to news.

What is a better approach?

Upvotes: 0

Views: 129

Answers (3)

rookian
rookian

Reputation: 1064

You could use UUIDs as your primary key. Because they are unique application-wide (if you create them with CakePHP), you could just use a parent_id column in your Photos table and get rid of the type colum.

Another approach would be MySQL Views. You could setup 2 Views (e.g. NewsPhotos, CompanyPhotos) on top of the Photos table. CakePHP handles (simple) Views like tables, so you could create easily Models & Controllers for this.

Upvotes: 0

leftclickben
leftclickben

Reputation: 4614

You should take the company_id field out of the Photos table and create two new tables, CompanyPhotos with id, photo_id, company_id fields, and another NewsPhotos with id, photo_id, news_id.

Then if you want to get the photos for a company you can do: select * from Photos p inner join CompanyPhoto cp on p.id = cp.photo_id where cp.company_id = ?.

And similary with NewsPhoto: select * from Photos p inner join NewsPhoto np on p.id = np.photo_id where np.news_id = ?.

Upvotes: 1

fedorqui
fedorqui

Reputation: 289675

It is always good to normalize databases. In the beginning it was just about tables with all data and it has evoluted to linked tables with common fields.

Hence, I strongly recommend you to have the table Photos.

After all, you have to make the basic question: can a photo belong to different news? Can a news have different pictures? If both questions' answer is "yes", you have a N:M relation, which is resolved with a middle table containing an id from every table.

Upvotes: 0

Related Questions