Create Database To Fit Data vs Make Data Fit The Database

Question

I'm newish to programming and would like to know what people have found to be the most supportable option or rather what people find more understandable. I'm the only one writing code at my firm, but gradually will hand this off to someone else in the future.

The Project

The project I'm working on is parsing a huge text file that comes in once a day. The data file uses previous day's data file as a reference so each data file builds upon the previous data file. Using documentation I'm able to parse the text file and populate a MySQL database. Once the text file has been read, the data in the database is manipulated and then dumped into many Excel workbooks. The text file has a huge amount of data and I only need a fraction of it for the Excel workbooks.

My Question

My question is what is the proper structure for this program? I see that I have two options.

1. Is it better to construct a database that fits the data. So create a database with more tables and fields then is actually needed and possibly a little cryptic in it's layout.
2. Is it better to manipulate the data while it's being parsed and put it into more readable, human database?

What I've Done So Far

When I wrote the first version of this program, I went for option 2. Since I didn't know much about relational databases at the time and wanted it very easy for me to understand, visualize, etc. However, now that I'm looking to expand the data that I parse from the text file and since each data file builds on the previous day's data file I'm leaning towards option 1 so later down the line I won't have to spend a day reconstructing the database.

Does this make sense? Please comment if it doesn't. Any help would be greatly appreciated.

thanks, Justin

Update

Let me start by saying the answers so far have been very helpful. Thank you. After reading some comments though I find that I have been a bit vague in my question. Let me try to be a bit clearer. I'm dealing with financial data of clients. Each account has its own Excel workbook that is used by the financial advisors to make projections and investment decisions. Additional worksheets in the the workbook are used to display the account's holdings and projected future to the client. The data that I get in each data file is separated by a record. Each record has a type...there are something like 13 different record types. Each record type is marked by a particular letter or number. Then after that letter or number there are a specific number of characters that represent that particular record. The record layout differs per record type and there is redundant information between record types which makes it additionally aggravating. Another point of contention is that if the financial advisor buys a new security, one that s/he hasn't bought before then the next business day, only in that day's data file, will the security's information show up (such as the name, security type, etc) and not again unless there's a change in that security. So if I ever need to rebuild the database I would have to start from day one.

So I'm taking this data file, parsing it and writing it to a MySQL database separated into about four tables. I use EF6 to work with the database, and so that was a reason I started with a simple, readable fields and tables. Hope this expanded explanation helps.

Answer 1

Keep it small and simple.

You mention your text file has a lot of data in it that you don't need. If you stuff all of that in your database, you'll have a much more complicated / harder to understand structure that the next person will have to decipher.

Can you imagine inheriting a large complicated structure where only 10% is in actual use?

See also this SO question and Jeff Atwood's blog article, Coding for Violent Psychopaths

By all means code up the stuff you need. Follow best database practices, normalize within reason, use views, foreign keys, and indices, and jealously guard data integrity with a vengeance.

Just don't build a bunch of junk "that you might need someday".

YAGNI is your friend.

Answer 2

It sounds very much like you're doing an ETL project. You've got all three attributes -- you've got your extract (pulling data out of the incoming file), transform (merging the data with yesterday's file), and you've got a load into mySQL. In fact, you've even got reporting (your Excel stuff).

There are whole companies (like Informatica) that have built products around making what you're doing easy. If this is a one-off kind of thing that you see no signs of ever growing into more and more data sources, then by all means you should follow the suggestions of others in this thread -- simpler is better. However, if this is just the first of many such projects, you really owe it to yourself to go learn about data warehousing, ETL, reporting -- Business Intelligence in general. Most warehouses start out as little one-off type projects like yours, and then they grow and grow until your little custom programs get completely unwieldy and unmanageable from an operations/run perspective. At that point companies start talking about "getting serious" about their warehouse, which means they start standardizing the processes for doing what you're doing (having a drop box, having a staging layer, having a standard/foundation layer, having defined places for doing loads vs. leaving things in files, etc.)

Answer 3

I do this sort of stuff regularly, for a major financial organisation.

I'd say your #2 is almost right, except for the "human, human database" part. Make it readable, sure, but prioritise PARSEABLE. Excel is the wrong output format, too, though you could certainly build excel stuff FROM your output format (output CSV, for example, then build on that if you like).

Lets say your records look like this:

S:1 T COKE P=20 V=1000

S:2 Q B=23 S=24 FANTA

S:1 T FANTA P=24 V=1000

S:2 Q B=24 S=26 FANTA

Let's say you want to extract all the Q records, and find the maximum B values, for each brand. So FANTA=26, based on the above.

First, you need to find the right records. That's just reading the data and looking for a newline followed by "S:", reading the next line, and reading until the next newline, if that next line was Q.

Then, you split the record by newlines, find the line starting with S=, and parse the rest into a value. You also need the brand, which is just the fifth field, in your list of elements that were separated by newlines.

Finally, you just need to keep a map of brands, and their maximum values seen so far. Each time you see a new value for a brand, check if it's higher than the previous value for that brand. If it is, update the value.

The critical part is: when you do this, WRITE ANOTHER SIMPLE, PARSEABLE, TEXT FILE, or again, maybe CSV, so long as your data is simple enough to genuinely map to CSV without data loss.

My output format for this would be as simple as:

FANTA=26

No more, no less. But sure, swap that = for a comma, and call it CSV if you like. YAML/JSON are also handy formats, when the need arises. The point is: keep it as simple as it can possibly be.

Write that data to a file which sits just beside your original input file.

This way, you get new data per day, and it's expanding on the old data, which is in a file right beside that. So you've added value, but you haven't increased the complexity.

That's a critical difference, from, say, putting all the information behind some database API, or displaying it in some java GUI, which isn't easily accessible to summarise at the end of the year.

Answer 4

If the requirements will change in the future ( which they always do ) you would be best served by having a fairly normalized DB based on Identifier values ( say customer ID , Order ID ..) By making sure you have a basic sound design to start with you'll be considered a genius when you say " yeah we can do that".

Answer 5

A well designed relational database will generally have tables made for optimal data storage. Of course there can be exceptions depending on your scenario (pretty much every database I've worked with has been denormalized at some point), but you must always try to favor enforced data structure over easy data retrieval. This will make your data safer and harder to get corrupted.

Since you mentionned you are using mysql, views will be your friend if you want to ease up data retrieval. Views are reusable queries made on top of your table or other views. You can have as many as you need and it won't affect how your data is structured in any way.