Tagged Questions
174
votes
15answers
54k views
Can MySQL reasonably perform queries on billions of rows?
I am planning on storing scans from a mass spectrometer in a MySQL database and
would like to know whether storing and analyzing this amount of data is remotely
feasible. I know performance varies ...
42
votes
4answers
4k views
Storing vs calculating aggregate values
Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?
For example, suppose I have widgets which users can rate (see schema ...
25
votes
3answers
10k views
Advantages and Disadvantages to using ENUM vs Integer types?
Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.
Is it better for this column's data type to be an int/bool (1 or zero) or to ...
16
votes
6answers
4k views
What problems will I get creating a database per customer?
I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases.
We are just ...
15
votes
3answers
3k views
How Does Table Partitioning Help?
I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
15
votes
2answers
25k views
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of ...
14
votes
6answers
6k views
Storing IP address
I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?
Should I support IPv6 as well? If so, what is the ...
12
votes
5answers
5k views
How do I force one record to have a true value for a boolean column, and all others a false value?
I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.
Basically, I want to guarantee that this query will always ...
11
votes
2answers
799 views
Fastest way to change a 600GB table indexed key datatype from INT to BIGINT
I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
10
votes
3answers
3k views
Best way to design tournament database
I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.
I have created a mockup ...
9
votes
6answers
5k views
Splitting Tables in MySQL. Good practice?
I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.
The tables look like this:
...
8
votes
4answers
818 views
eCommerce orders table. Save prices, or use an audit/history table?
Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product
A product can ...
8
votes
2answers
945 views
Database redesign opportunity: What table design to use for this sensor data collection?
Background
I have a network of approximately 2000 sensors, each of which has about 100 data points that we collect on 10 minute intervals. These data points are typically int values, but some are ...
8
votes
1answer
2k views
Generating Invoices and Tracking
Every 2 weeks, the system will generate the invoices for the companies.
Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
7
votes
4answers
606 views
How to create a database for unknown kinds of data?
I am in the process of designing a database for a new PHP/MySql-based application.
My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
7
votes
2answers
391 views
Data Compression Through Base36 Encoding
I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type.
I'm thinking about using Base36 encoding to accomplish this. Are ...
7
votes
3answers
1k views
Appropriate multi-language schema, or overkill?
UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/
I am building ...
6
votes
3answers
3k views
One Big Database vs. Several Smaller Ones
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,
...
6
votes
4answers
477 views
Purpose of IDs in a User Table Database
In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
6
votes
2answers
2k views
Is one-to-one relationship normalized?
Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
6
votes
5answers
19k views
Matching single column against multiple values without self-joining table in MySQL
We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:
user_id ...
6
votes
2answers
1k views
Database for building a realtime analytics system
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on
Real time counting of unique URI visit/PV by different ...
5
votes
2answers
1k views
Best way to handle dates prior to 1000 A.D. in MySQL?
I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000.
Is there a way that would be more convenient than either ...
5
votes
3answers
578 views
how do I model this in database?
I am using mysql as my backend database. Here is the use case;
A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
5
votes
3answers
652 views
Should I seperate frequently updated columns
I have a users table that contains users' information and a column named credits that is frequently updated.
On index page I'm showing list of users with basic user information but I don't need ...
5
votes
2answers
209 views
Is there a reason/benefit I am not aware of for this kind setup?
I now manage a MySQL database that was designed and created by another.
I am confused about a certain setup that the original developer created..
He did not use auto increments for table IDs. ...
5
votes
2answers
1k views
How to implement soft deletes?
What is your method of implementing soft deletes in a database?
Ideally the solution would allow for
decent performance on large tables
take relationships into account
take unique keys into account
...
5
votes
2answers
763 views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
5
votes
2answers
217 views
How should an object that can reference an object of multiple types be modeled in a relational schema?
Apologies for the obscure question, it may make more sense with a concrete example:
In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
5
votes
1answer
433 views
Full text search through many tables in MySQL
We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its ...
5
votes
2answers
2k views
how to enable temporal database capability in mysql.?
How to enable the temporal database compatibility in mysql database?
Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
4
votes
3answers
2k views
How costly is opening and closing of a DB connection?
How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL
... when the DB software is on localhost?
... when the DB software is on another machine?
4
votes
3answers
981 views
What is a common way to save 'debit' and 'credit' information?
I'm working on an accounting system, and for each transaction I need to save if this is either debit or credit. I can think of two ways (MySQL database):
METHOD 1
Amount (decimal)
Type (enum, ...
4
votes
1answer
3k views
Product Attribute List Design Pattern
I am working on updating our website's product database. It’s built in MySQL but this is more of a general database design pattern question.
I’m planning on switching to a Supertype/Subtype pattern. ...
4
votes
2answers
92 views
StackExchange clone: where should I add my indexes?
I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal?
Here is the schema in Rails format (SQL format below as well):
...
4
votes
2answers
791 views
Are there any tools to go from ERD to DB design and vice versa?
I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa.
For the record, the database i'm using is MySQL.
4
votes
2answers
1k views
What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?
I wish to future-proof my application. What is the best type of identifier to use?
I am considering:
64 bit Integer
UUID
64 character SHA256
I am not developing a distributed system, so is ...
4
votes
2answers
1k views
Database design for dynamic Meta fields of an entity that shall be queried intensively
I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, ...
4
votes
2answers
1k views
Creating partitions on a production database
I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.
I have two tables that involve a ...
4
votes
2answers
2k views
How to store formula method in the table
I am trying to figure out how to store formula method in the table (I could create category_formula table).
Each category will have different way of formula method to calculate the point and I need ...
4
votes
1answer
331 views
Are InnoDB tables harder to backup and slower?
I am trying to optimize our database tables. I have some sql skills, but complete newbie at db administration. Our db operations are pretty slow, and I have been assigned to review the storage engines ...
4
votes
2answers
903 views
Is my EER Diagram correct? Why use relationships?
This is the first time I've made an Entity Relationship Diagram (ERD) for MySQL.
Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and ...
4
votes
2answers
3k views
MySQL Database Schema for Train Timetables
I am interested in what a MySQL database schema would look like for a train timetable.
Generally this is presented in table format as the final result.
Train No. 11111 22222 11111
Train Day ...
4
votes
2answers
777 views
Partitioning a table vs moving entries to another table
I don't understand how partitioning helps me more or less than using a separate table to store older records.
I need to maintain a list of stock entries along with historical records about the stock.
...
4
votes
3answers
1k views
How do you structure a database that organizes post by location like craigslist?
I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to ...
4
votes
1answer
117 views
Probable circular dependency, but query still runs
I built this ER diagram in MySQL Workbench and it will run and generate the database. My concern is that the relationship between the employees, departments, positions, and employment_statuses is a ...
4
votes
1answer
573 views
What relationship type to use for this messaging system?
I am trying to make a small messaging system. It should have these properties:
One user can send a message to one or many users
Messages can be sent only to registered users (i.e. to an existing ...
4
votes
1answer
434 views
MySQL database normalization 3NF
I have homework where I need to create simple web interface and a DB normalized to 3NF. I've chosen ISP client selfcare as my database purpose. Web interface is no problem, the normalized DB is. After ...
4
votes
1answer
205 views
MySQL table design of logging tables
I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization.
Currently we have a table that logs session data, it writes about ...
4
votes
1answer
737 views
Designing a database for a site that stores content from multiple services?
I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
