Mysql – Database design for 2 1-to-many

database-designMySQL

It's the first time here so I don't know if it's the right place to ask.. I'm not good in database design, just learning, so I'd like some advice if possible.

The case:

I'm building a really small application, a plugin, who's tables will likely have not too much records in real life (less than 50 in extreme situations), so performance is not crucial.

  • The user can create n Groups. A Group should have a 'hook' field.

  • For each Group, the user can create n BannerBoxes. So, a Group can have many BannerBoxes, a BannerBox can have only one group. Therefore a 1-many relation.

  • There can be n languages, and the Language table is already in place and has id_lang field. The language-group relation is 1 (language)-many (groups)

I'll often have to ask the db for all BannerBoxes saved for a certain language, grouped by Group. So, first find all Groups of a language, then for each Group, find its BanneBoxes.

I've thought about 2 solutions, but I cannot decide which could be better, and since I'm definitly a newbe, both could be just wrong.

a. Group entity with [hook, id_lang] fields; BannerBox entity with an [hook] field (desides the others). So when selecting, I can join the Group table with Language table on id_lang and then join with BannerBoxes on hook field.

b. Only the BannerBox entity, in this case with both [hook, id_lang] fields. I could join with Language by id_lang and then group by hook.

I also guess that a Group_BannerBox table [id_group, id_bannerbox] would be useless since I think it's a solution for many-many relations, am I right?

So, what would you do? a. or b.?

Thank you!

Best Answer

  • Create a User table with a UserId PK and any other user related fields.
  • Create a Language with a LanguageId PK and the name of the language.
  • Create a Group table with a GroupId PK, UserId FK, LanguageId FK, Hook? and any group specific fields.
  • Create a BannerBox table with a BannerBoxId PK, GroupId FK, and any other Banner Box specific fields

This will let you get all the information you need assuming that languages are consistent for a group.

I don't know what your Hook field is so answering @ypercube's comment would be helpful. In this answer I assumed it was some sort of business logic that was relevant at the group level.

Related Question