Yii, Database Design


I don’t know how to design my database. I have table: team, player, picture. Picture table has 3 colums:id, owner(foreign key), and image. Team and player can upload image and set image as profile image. All images which is uploaded are saved in the picture table. So foreign key “owner” in the picture table is able to reference team’s primary key “ID” or able to reference player’s primary key “ID”.

I don’t know how to recognize which one the image reference, team or id.

Should I make 2 tables, like teamPicture and playerPicture? Is there any way to do this by not making 2 picture tables? how do I create my model?

If you’re insistent on using the database for storing images (rather than the file system), you could use a single table and have an additional field containing the image type. You could use a MySQL enum field to identify which table the image belongs to, either ‘team’ or ‘player’. You won’t be able to set a foreign key in this table though.

If you need to be able to use foreign keys in the picture table, you’ll probably need separate tables for team pictures and player pictures.

There’s not really a wrong and right answer, you’ll need to choose the best fit for your application. A single picture table might be easier to maintain, especially if you start adding other categories of picture, so that’s probably the route I’d take.