Database design questions


I have some questions on how to design my database:


1) I completed all work in Excel so I know what formulas and what output I want. Is it easier to do various transformations in a new that same table as the data? This screenshot is what my tables look like. Raw data is dumped in the A:D and E and F do transformation on the raw data dumped in then do lookups. Do I follow the same logic in my database? Make new tables and relationships? How would I take this from a flat to relational database?

Screen Shot 2021-09-27 at 7.49.43 PM.png


2) I imported into Access the output I have in Excel though I only pasted just the 350K lines with no format/calculations/lookups, only just values and the database is already 203MBs size. Why? I know Access has a 2GB file size and worry if I need to make queries and add all the tables I need I can hit this limit quickly overtime.


3) Is Access my solutions for my issue? My Excel/file database freezes when I dump in new data because with the design it seems to have to go through so much in memory and lookups to the other tables. I process all I need then make a connection to the data in an Excel workbook so it can be used by the end-user and myself as it's output as a Pivot table.


4) I make a local file but wish to share with another colleague who can use the exact same design just with his local data. Do I send a file with a few lines of data then dump his in? Is this the best way to share a "shell" of a database so he can dump his data in the exact same way I do mine?

Screen Shot 2021-09-27 at 7.35.39 PM.png

0 Replies