1

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!

Improve this question
6
  • What is hook? Is it a unique property of Group? (it seems to be since you are using it in BannerBox instead of id_group).
    – ypercubeᵀᴹ
    Aug 11, 2015 at 16:38
  • About the languages, can the BannerBoxes that belong to the same Group, have different languages? Or they should all have the same as their Group?
    – ypercubeᵀᴹ
    Aug 11, 2015 at 16:39
  • :) thank you all for rapid and many answers! Hook is a field of Group, but cannot be used as primary id, as far as I can say. In practice, a hook is a 'place' on a website where I can put n bannerboxes. So itself, Group could even be the only field of a Group record. In fact, Group could be just Hook instead. BannerBoxs belonging to the same Group/Hook cannot have different languages. I'm using mysql, but this is just a general bd design question.
    – Luca Reghellin
    Aug 11, 2015 at 17:23
  • @Stratboy so is hook equal to: max banners, left side bar, or max banners on left side bar?
    – Erik
    Aug 11, 2015 at 18:08
  • @Erik: mmm please excuse me I can't understand your question. Probably being a newbe I lack some db terminology. I try to explain the real use case: hooks are a programming contruct used on the platform I'm building a plugin for. They help programmers to dynamically place pieces of html code in certain places on an page. For ex, a hook can be called 'headerhook', 'sidebarhook', etc.. In my case, the platform user will select a hook and then will create and assign bannerboxes (html boxes for showing banner slideshows) to that hook > that means, for ex: 'place those boxes on the home's sidebar'.
    – Luca Reghellin
    Aug 12, 2015 at 7:47

1 Answer 1

Reset to default
2
  • 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.

Improve this answer
4
  • And note that Group and User are reserved (and almost everywhere reserved) keywords, respectively..
    – ypercubeᵀᴹ
    Aug 11, 2015 at 17:02
  • @ypercube very true. Since the OP identified themselves as a newbie I'm glad you pointed that out otherwise my answer might have lead to frustration instead of assistance. I included them because they were the terms the OP used and the RDBMS I am most familiar with (SqlServer) allows you to escape reserved words if you want to use them for table names etc.
    – Erik
    Aug 11, 2015 at 17:11
  • @ypercube Thanks for editing my answer to highlight the column and table names. I'll have to remember that mark down for future answers.
    – Erik
    Aug 11, 2015 at 17:12
  • Yeah. All DBMS I know allow escaping, with either double quotes (the standard) or/and some other character. But it's cumbersome to have to quote every occurrence of a table. That's the only reason I kind of like plural names in tables (because of: Group, Order, User ;)
    – ypercubeᵀᴹ
    Aug 11, 2015 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.