Hot answers tagged

14

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc. Storing JSON blobs in the database will kill performance when you're performing searches, so I ...


9

Personally since you don't have a relation beyond barcode --> document I don't think a relational database is the best fit. If your requirements are really as simple as: Find a document Serve document to user Then any Key/Value store should work. This wikipedia article talks about some of the theory for Document Databases as well as some pro/cons vs ...


5

Generally speaking when you want to fix a big-ball-of-mud architecture it is best to focus on one small aspect that you can fix relatively quickly with maximum payoff. What that thing is will vary depending on the system. Once you identify that thing then implement it and look for an opportunity to fix something else. Over time your big-ball-of-mud will get ...


4

I would heavily advise you not make 142 tables, it will be a nightmare. But don't necessarily use JSON, either. Instead, you should have one table containing categories, and another table containing the items. Link items to their respective categories by using a foreign key column. Your items table could have a category_id column, for example. Do not store ...


4

NOT an answer - but too long for a comment. [opinion] Faced with (luckily for you) a choice between MySQL and PostgreSQL, I would unhesitatingly go with PostgreSQL. CHECK constraints - silently ignored by MySQL. Incredibly reported as a bug 8 years ago - at least it's documented! :-( "The CHECK clause is parsed but ignored by all storage engines". CTEs ...


4

Your existing index on DATE is obviously useless for the query. The first obvious step for your query: SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2'; is an index for column_a or column_b (which ever is more selective) or possibly a multicolumn index on (column_a, column_b), like: CREATE INDEX tbl_a_b_idx ON tbl(column_a, column_b); ...


4

Will you be ranking the billions of web sites? If so, then on-the-fly and recompute-all are out of the question. Instead, have a background task working on the problem. For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc). Put the ranking in a separate table, with only website_id ...


3

Trying to do FKs will just frustrate you - you don't actually need it. If you handle versioning by using an InsertDateTime concept, then you're basically describing a Type2 dimension as used by many data warehouses. There is quite a lot of material out there about tuning systems that sit over the top of data warehouses, but from a T-SQL perspective, ...


3

Generally speaking I would recommend a full fledged relational database. This will give you everything you need, and allow you to grow gracefully. As far as what exact engine to use that depends on personal preference/budgetary constraints/hosting requirements/compatability with the application layer/etc. Personally I would probably use the Microsoft ...


3

Another aspect that the other answers are not addressing is your (and your group's) expertise. What you have described is a very generic RDBMS workload. Most of them are capable of storing the binary data as well, up to limits that will vary between different implementations. The best solution for you is the one that will be the easiest for you to run and ...


2

All you need is bar code to file I would just store bar code and file path in XML. When you load the application you read the XML into a dictionary. Use a dictionary lookup get the file name and then just read the file from disk. You you can use any database that will store a binary (they all do). This is just a simple table. It is kind of a pain to ...


2

Even more solutions / points to consider In addition to the suggestions offered by fellow members of the community, I would like to also give you some further points for consideration, which are a number of important learning points that I have picked up over the years while working on similar projects that may have had very similar pitfalls or ...


2

Given your "irregular" data, you might want to look at Magneto (see here and here). Originally designed for the clothing industry which can have many different products with many different attributes. It might just be a fit for your needs. There is a community edition so you can look at the code and (esp.) the database table structures and adapt to your own ...


2

SQL Server has transparent database encryption, which provides the functionality you are looking for. TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using ...


2

It seems that you have some analytics data. So basically all you need is a columnar database. E.g.: Open source (NoSQL): druid.io Commercial on-site (SQL & NoSQL) : MemSQL, VoltDB, etc SAAS: Amazon Redshift, Google BigQuery They have append-only semantic, archive your content to save the space, group by query is available (in druid.io it's called ...


2

I did something like this with MySql and you can save directly CSV files in MySQL. I think Apache Cassandra is a good choice for you CQL3 is very similar SQL, but with some limitations that come from the scalability (most notably: no JOINs, no aggregate functions.) CQL3 is now the official interface. Don't look at Thrift, unless you're working ...


1

If you wish to compute statistical data a Relational Database is generally better than a non-relational as all the links for your statistics will be inherent in your table design. Millions of rows will require watching your performance and adjusting appropriately but is not in the danger zone architecturally speaking, and since your data is being inserted ...


1

The specification TPC BENCHMARKTM H, Standard Specification, Revision 2.17.1, can be found here. See section "1. LOGICAL DATABASE DESIGN / 1.5 IMPLEMENTATION RULES". Indexes can be created on combinations of fields defined in 1.4.2.2 and 1.4.2.3 and on date fields. Indexes may only contain fields of one table. So indexes can be built on the following ...


1

Since you're looking at storing and querying 135,000,000 * 30 * 10 records that likely wouldn't benefit from traditional RDBMS features, I think Hadoop would be the way to go. My experience is exclusively with Microsoft Azure - if you're not attached to Amazon you might check it out. Either way, Hadoop is open source so the majority of operations and ...


1

One answer I have given for a similar situation is here: Best way to design a database and table to keep records of changes? The third and fourth bullet points address your request for single query access to current / historical data and preservation of foreign keys. Sometimes it's a help just to know that what you want is possible.


1

MySQL Perspective If you wish to load Documents .docx and .xlsx files into a MySQL Database, you will have to use the LOAD_FILE() function. Here is what the LOAD_FILE() Documentation says: Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to ...


1

I would say that your second suggestion, haivng a TechSpecID in the Vehicles table, makes the most sense. A way of thinking could be, is a Vehicle implementing a TechSpec or is a TechSpec implementing a Vehicle? I.e. what comes first (the Technical Specification) and what is 'using' your Technical Specifications (the vehicles). But, in the end, it's all down ...


1

In my opinion you must be, or you need, a DBA. Not a single rdbms can solve problems with big data out of the box. In your case partitions can be useful for example. So data compression, etc... I can just suggest 3 alternatives: SQL Server Enterprise Edition Oracle Enterprise Edition Postgresql And the skills to manage data.


1

first question are what are the requirements for all you know it could be a BigData project next Question should always be RPO/RTO I would then question required throughput estimated users typical usage scenario Peak usage scenario also what the Data Access Layer the Developers are using direct sql calls or some form of ORM then evaluate the ...


1

For example, I have a table which contains a column whose format is BLOB. The result I would like to get is to extract the content of the BLOB into CSV files. What is the most elegant method to do this? BCP OUT will be your best choice. Make sure your database has BLOB data (Image and/or TEXT datatypes). Also, select into should be set to TRUE ...


1

You should store any value using its ISO measure unit. Calculations and conversions should be done at the client side. Don't expect any performance gain over client side conversion when using computed columns. As far as passing in data in any measure unit, I advise against that design: make the client pass in the data in the ISO measure unit and do any ...



Only top voted, non community-wiki answers of a minimum length are eligible