2

I'm not so good in db/tables design, I'm mainly a frontend web developer, but sometimes I do really small apps. Always I find myself wondering if my thinkings are correct.. So I try to ask here :)

The db is for an app for helping a local association to manage its associates and their annual cards/subscriptions. Basically:

  • Once associated, the person is an associate forever (no need to renew every year)

  • Still, the associated needs an annual card to participate to the association's activities. So he/she needs to eventually renew his/her subscription to the activities, yearly. It's not mandatory, though: he's not forced to renew every year. He can be associated without having the current year subscription/card.

So, one associate can have many cards (one per year). One Card can have only one Associate.

I imagined just 2 tables:

Associates
   id
   name
   surname
   etc...


Cards
   associate_id
   year

It should be enough. But, should I maybe instead use a Year table? I'm not really sure this would be any useful in this specific case. Also I would then have a many-many relation between years and associates, so I guess I would need a third table (YearAssociate).

What do you think?

5
  • 2
    I would then have a many-many relation between years and associates, so I guess I would need a third table (YearAssociate). You already have it - the Cards table sets a relation between years and associates. You do not need additional link table - it will be a duplicate.The only alters you need maybe is to create a Year table and alter Year field in Cards table to YearID field referenced to Year table, but I think a ENUM type of Year field in current structure is enough.
    – Akina
    Commented Oct 4, 2018 at 8:09
  • Mmm ENUM, interesting. Never used. But why? Do you mean that I should limit the values to dddd for example? I'm using mysql, didn't found how to use regexp in ENUM definitions. Commented Oct 4, 2018 at 8:53
  • Do you mean that I should limit the values to dddd for example? I mean you define the field as ENUM with a literals list of possible Year values. Year ENUM('2016', '2017', '2018', '2019', '2020') for example. how to use regexp in ENUM definitions ENUM do not need in additional regex check - it accept only values present in its definition list. Additional check the year is not in future (by trigger logic, for example) is enough.
    – Akina
    Commented Oct 4, 2018 at 9:20
  • Year ENUM('2016', '2017', '2018', '2019', '2020') would not work since I should list all te year from current to forever. Commented Oct 4, 2018 at 9:22
  • 1
    Well, lets Year to be an INT type with additional trigger check its value is not in past and is not too big. The list of possible values (when use in combobox) must be build on the client side in that case. In any case you do not need in additional Years table.
    – Akina
    Commented Oct 4, 2018 at 9:27

1 Answer 1

1

No, adding a year table would add nothing to this data model.

Think about what a Year table would be like. What columns might it have? It really only has the year number itself - 2018, 2019 .. 2031, 2032 etc. Cards would then have a foreign key to this table, which the currently existing column Cards.Year already represents.

If you were doing a logical data model you may choose to put the Year entity type in it. For a physical database implementation & table design there is no need to implement a Year table.

One caveat - if your years are not calendar years, and you care about the calendar dates that each membership year starts and end, then having a separate Year table makes sense. Think about the tax year. It starts in, say, calendar April and runs to the following calendar March. So tax year and calendar year do not cover the same set of days.

Your Answer

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

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