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?
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?