How to create a good and logical database structure? I have struggles

Need help Database
1

Hello dear people from this forum, I really appreciate your valuable answers - I have learned a lot with all kinds of answers form this forum. Thank you!

I am struggeling with creating a good, consistent and logical database structure for my project. Maybe someone here can point me to a better direction:

The project:
I have built a multistep form - a business calculator - where a user can calculate his companies net worth by answering many questions.

Currently I have a single Data Type called financeinformation with about 30 fields (the answer to one question represents one field) like:

  • revenue_oldest
  • revenue_middle
  • revenue_current
  • revenue_prediction
  • email
  • name
    …

The calculator is not beeing finished yet and is likely to grow at least double.

Also I want to make some calculations in the background and this data will be saved as well e.g. the average of the last 3-year revenue or wheter the current year is the highest and so on…

What shoud I do? Should I create for each topic another Data Type within financeinformation and link to it? What is a good field-size? Are 60 fields too much for a bubble database?

What I can imagine to do is something like:

  • financeinformation
    • revenue_information
      • revenue_oldest
      • revenue_middle
      • revenue_current
      • revenue_prediction
    • personal_information
      • email
      • name
    • …

Does this make any sense?

Can you recommend any blog or book to study this?

Thank you!

2

Mo’ fields… Mo’ problems. Similar words were once said by the Notorious philosopher B.I.G.

Exactly how many fields will cause you problems depends on the type of fields and specific implementation. I would give a read over this post (which links to an article) on Satellite Data Types: DB best practice? User type different than User profile type? - #2 by mikeloc.

1 Like
3

FWIW:

Your financeinformation table basically contains revenue figures for one unique year, correct?

And you want to be able to show the user (for example) the 3 year average of their revenue_middle values, correct? For example, if they entered in:

2020 $4000
2019 $3000
2018 $2200

You want to show them:

2018-2020: average revenue_middle is $3067

IF this is the case (and of course, I’m assuming a lot) you need to do a few things:

  1. Add a year field to your financeinformation table.
  2. Actually…there is no 2.

If you include year in the table, you could easily calculate ANY 3-year average just by asking the user for a start year then retrieving their rows where:
financeinformation’s year = what they keyed in
OR financeinformation’s year = what they keyed in + 1 year
OR financeinformation’s year = what they keyed in + 2 years

And just calculate the average of the retrieved values.

In other words, you won’t need a second table…just use your source data for the calculations you want to show. This solution does require more resources for the calculations, but I personally am NOT a fan of storing calculated values. I’m in the “dynamically calculate summaries” school of db design rather than the “store calculated summaries and update them when needed” school of db design. The former is slower but shows figures from the raw data so they automatically reflect “reality”; the latter is faster but requires more updating in the background.

On a side note: I would user the User table for their name and email, then store the User’s unique ID in your financeinformation table. Saves a lot on space and redundant data.

On another side note: I don’t think 60 fields is anything to worry about.

EDIT: Sorry, I should also mention that my suggestion would let your user calculate an average for any number of years, not just 3. You’d also be able to show max, min, median, etc. (the joys of calculating dynamically rather than storing it!)