0

I'm in the process of designing one database that will replace lots of CSV files that I currently use for data storage, which are starting to get messy and inconsistent. I am using C# in Visual Studio. It is for Bricklink/Lego data, and I'll I'll just explain the section that I need help with below, simplifying the actual figures:

The part of the database I'll focus on has 3 tables: -Parts contains PartID and about 30 other fields (e.g. mass, averageSalePrice). There are 50,000 Parts. -Store contains StoreID and about 10 other fields. There are 1000 Stores. -StoreParts links the two in a many-many relationship. It contains PartID, StoreID, Date, Price and Notes.

Now here is my issue: Each store has 10,000 parts. So there would be about 10 million records in StoreParts (more if I record multiple dates). One query that I am likely to run would need to retrieve all of the parts for a given store and compare their Price to the averageSalePrice in Parts. I feel this may run very slow as it would be going through 10 million records of StoreParts to find the 10 thousand parts.

When I was using CSV files to store the data, I had one file for each store/date, so it only had to open that file with the 10,000 parts. I feel this would be more efficient than having to find one store's parts in the list of 10 million or more.

Is there a way I can set up my database so there is a separate table for each store? I feel that this would be more efficient to search, but from my experience does not fit with best practice for database design, as I would have 1000 store tables. If I consider recording store data on different dates (e.g. 1 store has the price of all its parts on 100 different dates), then things could get way too big and slow.

I would welcome any advice on this, as I would love to do this properly and not have to have CSV files sitting around all over the place as I currently have. Thank you.

  • If you have indexes on the appropriate columns (PartID, StoreID, Date) it will not be slow to go though 10M records - the database will not do a full scan of the entire table every time, the index will be a binary tree (B-Tree) and it will be very quick to get to just the rows for a particular query. 1000 tables for each store sounds crazy ;-) – Ian McGowan Mar 30 '19 at 3:19
  • My advice is to go with an RDBMS - yours is the perfect use case for one. Take a look at this and it should help - have one table for inventory and a joining table between inventory and shop/store. Also, you might want to look here. – Vérace Mar 30 '19 at 10:53
0

A modern relational database should be able to handle 10M records in a table with no particular problem. You may have to spec the hardware memory up if your queries aren't performant. I think the most expensive part of this would be the initial ingest, where you have to parse, organize, and import the CSVs. If you get new data regularly in the form of CSVs, that may be a concern, but that can usually be addressed by writing the ETL code in something efficient like Go.

My next suggestion would be using something non-relational. I see where you do joins in these datasets, but a non-relational dataset that just had records of a specific part in a specific store might not be unreasonable. I know MongoDB or other NoSQL options would likely work for this use case. NoSQL databases work very well with large amounts of data, but don't let you do much in terms of joins, but if this is the extent of your dataset, that very well might work for you.

I tend to lean toward Elasticsearch for datasets like these. Calling it a database might start a fight in certain circles, and this isn't the primary intended use for Elasticsearch, but I've found it work well as a sort of NoSQL database. It's search API is easy to integrate with applications and it's very easy to scale up to the performance you need.

tl;dr: Don't knock a relational database with three tables like you describe--I think it'd work fine. But if that's the only join you'll ever need, flatten it and put it in a NoSQL database like Mongo, which should make it easier to achieve high performance. Depending on what you do with the data, Elasticsearch might be worth looking into.

I realize this is a rather general answer, but it's a rather high-level question, so I apologize.

|improve this answer|||||
  • Thank you for that. I've only learnt databases in an introductory uni course, and it was all relational, so I didn't realise there were other ways. I'm quite used to writing C# code to get/analyse the data from multiple CSV files, so I'll look into the other options you described. Thank you for your help. – YesThisIsMe Mar 30 '19 at 1:58
  • If you found this post helpful, please upvote it. That's both a help to others who may have similar issues (higher rep questions get higher on search lists) and also a small bonus for the effort which the poster put into answering your question. – Vérace Mar 30 '19 at 10:45
  • @Vérace I don't think he/she has the reputation to upvote yet. Though, I think "Accepting" the answer is possible if the poster chooses. @ YesThisIsMe I glad it helped a little. Good luck in future endeavors. – TopherIsSwell Mar 30 '19 at 12:21
  • But surely the user can upvote their own questions' answers? It's a bit like new users can't comment normally, but can on their own questions? I may be wrong - I try to avoid the organisational layer of SO, but I'm fairly sure that I'm correct? – Vérace Mar 30 '19 at 14:43
  • I can't upvote answers to my own questions. I feel it's a bit heavy-handed for spam/crowd control personally, but I digress. – TopherIsSwell Mar 31 '19 at 21:41
0

Your data is very relational. I'd stick with a Relational DataBase Management System (RDBMS). Just remember: "database agnostic code is a myth". Once you pick a DB, you should stick with it until you find a major reason not to.

10 Million Rows !!!

yawn.

Not only can modern RDBMS handle a 10M row table without a problem, they can handle 1 Billion row tables without a problem. (10M rows per store * 100 dates).

Proper Indexing is one key.

This answer assumes you are storing all 1 Billion rows in the database.

Question

Is there a way I can set up my database so there is a separate table for each store?

Yes. Better yet, you can have these 1000 "tables" act like a single table. In the database world, this is called Partitioning. In your case, you are asking "How do I partition by StoreID?". Easy question to answer. But, probably the wrong method to PARTITION your data.

Since all of the data is in one table, you can ask questions like:

  • Who has the best price for these ItemIDs?
  • How has the price for this ItemID performed over time?

Partitioning by DateOfPrice ( Date is a Keyword, don't use it as a column name) will allow you to manage the historical data very efficiently. Need to remove all 10 Million rows for Jan-2018 data? Drop the partition for Jan-2018.

You will need to run benchmarks to find the best combinations of Indexing and Partitioning.

Suggested Improvements

Historical Averages

As time progresses, you could keep the avgSalesPrice and DateOfPrice in a table. These could be classified as Slowly Changing Dimension Type 4 (SCD Type 4)

Of course, you could always do the same thing to your StoreParts table also. ( StoreParts has Current data while StorePartsHist contains the historical records)

It will depend on the other questions that you ask of your data

Loading Records

A FilesLoaded log table will help you keep track of what CSV files you have loaded and which ones you haven't. If you desire, you could keep a copy of the CSV in a CLOB column. This would allow you to re-process the data. (This capability may not be needed for your situation. But, it is an idea that could be used)

Additionally, this table could be used to find out "What date is the most recent Price List for StoreID=1234?". With that information, You can narrow things down to a single partition (by DateOfPrice) and a sub-set of rows (StoreID=1234) very quickly.

JOINs and VIEWs

Make sure you understand the concept of JOIN. Coming from C#, you probably fetched individual pieces of information "on call at a time".

With multiple JOIN statements, these SQL statements may seem to be complex. Additionally, you may want to reuse that statement for other applications. If you want to re-use (or hide the complexity of) an SQL statement. You can hide it with a VIEW.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.