1
  • context :

    • Teachers create problems to their Students
    • A Problem have Numerals(Questions)
    • A Numeral(Question) have Steps to guide the student to the answer
    • Choice represent the type of mathematical abstraction that fits better
      • Solve: (unknown variable, starting expression)
      • Simplify: (reference, target)
      • Plot: (slope,b)
  • Base Models and Relations:

    • Problems: one to Many Numerals(Questions)
    • Numerals: zero to Many Steps
    • Steps:
  • Requirements:

    • Numerals and Steps now have a choice which where described above, currently just 3 choices but it's expected to grow. only one and at least one choice
    • Depending on the choice the numeral or step will add new attributes
    • for now choices do not have any relations between them
    • choice in Numeral could be different to the choice in Step and vice versa (see Example)
  • Example: did my best to make a close abstraction to show the idea, I've made a JSON Structure, almost identical to the structure used in my front end. Maybe the example is a little bit dumb but it gives a taste.

  • My Attempts: For simplicity , Models are shown with the minimum attributes

    • 1st: 1st design

    -My concerning with this approach is that the amount of FK in the Numeral and Step model. Since only one choice is possible, i would have choices-1 amount of useless columns per record

    • 2nd: 2nd design

    -I'm programmatically checking for the choice field in the Numeral or the Step, and then using the proper table to fill the data, this second attempt started as a solution to fix the empty columns in the 1st approach

    -My concerning with this approach is that the amount of tables will grown 2*n, since for each choice i need to create the corresponding table for step or numeral

I'm open to suggestions or ideas about this attempts.

Edit: Thanks to @J.D for suggesting this idea. i've modified a bit because i need to keep track of the choices and their fields somewhere somewhere. I think this solution is easier to mantain and removes the need to add tables in case of new choices

  • 3rd: 3rd design
2
  • What are ANumeral, BNumeral, CNumeral, AStep, BStep, CStep? Can you use some real examples so we understand more of your problem? Also, there are many questions and answers on subtypes; there's probably an answer there somewhere. The problem with database-design is that while it's mostly science, it's also partly art, and with a dash of opinion thrown in. This makes it hard to definitely give an answer to any design problem. The answer is usually, "It depends." Commented Mar 5, 2021 at 7:15
  • will add some examples, working on it
    – joaortizro
    Commented Mar 5, 2021 at 14:13

1 Answer 1

0

As Colin touched on, it's hard to follow what schema problem you're trying to solve with just generalities. Unfortunately the devil's in the details with this kind of thing, since one detail could drastically change the best approach.

Looking at your database diagrams at a high level the tables A, B, and C in the first diagram look like the same entity in regards to their purpose, but varies in value of their columns, most likely?...same when comparing tables ANumeral, BNumeral, CNumeral to each other, or when comparing AStep, BStep, and CStep to each other, in your second diagram. The question is why couldn't each of these sets of tables be just one table each (or possibly even one table altogether)?...e.g. you likely could have just a single StepAttributes table that Step joins to.

I did notice in your example the only difference between two tables of the same set are the attribute data types. If that's your reasoning for making multiple tables, instead just structure your single attribute table (e.g. StepAttribute) to account for that which can be done two ways.

The first way by having three columns, one being the AttributeName, the next being the AttributeValue (which will be a string type field, like VARCHAR, to accommodate any value), and the third being the AttributeDataType which stores the native data type of the attribute so you know what to appropriately cast it to when you consume it.

The second way is you can have a denormalized attribute table that has a nullable column of each data type with generic column names, such as DateAttributeValue, IntAttributeValue, VarCharAtrributeValue etc, and a column to specify which data type the value is / column it's stored in. Some systems even design such a table like DateAttributeValue1, DateAttributeValue2, DateAttributeValue3, IntAttributeValue1, IntAttributeValue2, etc so they can support storing multiple attributes within the same row.


Thanks for updating your question with more details. So at the least it looks like each sub-table to Numeral vs Step are a bit redundant in your second design, and I understand why you normalized them that way based on your first design. You could actually combine the best of both worlds though and just have a single Plot, Simplify, and Solve table with the foreign key reference field in those tables going back to your Numeral and Step tables by storing both IDs in that column and calling it something like NuneralOrStepId. Then you'd need an additional field on those tables called like IsForNumeral or ParentType (come up with a better name than mine lol) which would distinguish which base table it joins back to. Then you have no waste in fields and reduce your redundancy as well.

It depends on how many of these sub-tables you anticipate having to generate and manage (right now 3 is completely manageable) but if you think that's going to grow into over 10 tables or so, I'd still consider my first suggestion with one generic table of AttributeName / AttributeValue pairs. You can add any additional columns to this structure that make sense as well, such as the ParentType column to distinguish which parent table the Attribute belongs to.

8
  • Thanks for your ideas, I've added context and an example . Indeed A,B,C which are the choices varies in columns. In regards to the Numerals and Steps, they have different columns by default. So i think having two separate models fits better.
    – joaortizro
    Commented Mar 6, 2021 at 4:33
  • @joaortizro Thanks I've updated my answer with additional suggestions and information.
    – J.D.
    Commented Mar 6, 2021 at 14:37
  • Did my best to understand your first suggestion , i decided to make a 3rd design based on your ideas,
    – joaortizro
    Commented Mar 7, 2021 at 7:34
  • @joaortizro Cool the only suggestions I have is your belongsTo column should probably be a boolean or int based field as that's more manageable than a string (in case the table name changes, etc). So if there will only be the two parent tables Numeral and Step then I'd make it a boolean field called belongsToNumeral. Otherwise, if more parent tables are possible in the future, then I'd have 1 meta-data table AttributeParent that had the name and a unique ID for each parent table, and then that field in your Attribute table would be an int referencing that ID. Also because...
    – J.D.
    Commented Mar 7, 2021 at 14:08
  • 1
    Yes, indeed there should be a one to many between Numeral and Attribute , as well as Step and Attribure. Thank you so much for ur suggestions, really like the design. In regards to the numeralOrStepId working together with the belongsTo ,i will check it, in theory seems really good, since im using Django in my backend and i 've never seen a FK as a reference for two models but i guess is doable. I really appreciate your suggestions and effort , thank you.
    – joaortizro
    Commented Mar 8, 2021 at 1:42

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.