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

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


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); ...


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


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, ...


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

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

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


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

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

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

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

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

What are the clients? PHP? Java? (etc, etc) There are potentially serious hacks that can do things like "SQL Injection" through them. That is perhaps more common than IP restrictions. Do you have root only from some secure place? Does it have a hardened password? Are all the application 'users' granted access only to the desired database, and with ...


1

It looks like you might need to use a DDNS service from a service such as www.no-ip.com, then filter on DDNS name. The client will have to update their IP on the dynamic dns system though. Have you considered running the db engine on a non standard port with very strong passwords and all the security best practices presented, then letting the clients ...


1

Here are a couple of options that help with security. A simple step is to not use the default 3306 port. Also, use very hardened passwords and if possible, change passwords often. A trick that I have done is instead of having your application communicate directly to your mysql server, have it communicate via PHP to an Apache server running on the same server ...


1

I don't understand why you're asking this question. If you're buying in a service, then backups should be part of the SLA and not your responsibility. If it's not part of the service, then why are you using SaaS? On the other hand, if you need to be backing up something, why are you backing up every hour? Technically, if your application is running on a ...


1

I answered a similar question in Aug 2011 : Which DBMS is good for super-fast reads and a simple data structure? Since you are asking about MySQL and which storage engine. To be honest, it is hard to say because there are rare occasions when MyISAM can outperform InnoDB when it comes to SELECTs. Here are some of my past posts on this controversy Sep 20, ...


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


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

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



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