Tagged Questions
152
votes
14answers
34k 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 ...
37
votes
4answers
2k 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 ...
14
votes
3answers
3k 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 ...
13
votes
6answers
2k 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 ...
11
votes
6answers
3k 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 ...
11
votes
2answers
2k 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 ...
11
votes
3answers
11k 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 ...
10
votes
5answers
2k 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 ...
9
votes
5answers
3k 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:
...
9
votes
3answers
1k 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 ...
7
votes
4answers
284 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
404 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 ...
7
votes
1answer
731 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 ...
6
votes
4answers
389 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
298 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 ...
6
votes
2answers
937 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
2answers
588 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
3answers
1k 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.,
...
5
votes
5answers
10k 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 ...
5
votes
3answers
432 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
2answers
171 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
499 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 ...
5
votes
2answers
369 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
187 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
2answers
708 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
2answers
276 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
514 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 ...
4
votes
2answers
679 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
408 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
1answer
205 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
2k 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
3answers
324 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 ...
4
votes
3answers
614 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
2answers
385 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
...
4
votes
1answer
298 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
1answer
172 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
123 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
463 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 ...
3
votes
3answers
459 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?
3
votes
2answers
257 views
Database Design (Stores + Products)
I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200.
My question on DB design, is how do i keep track of which products ...
3
votes
3answers
963 views
indexes, foreign keys and optimization
Not a DBA so pardon my ignorance...
I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows.
When retrieving ...
3
votes
2answers
107 views
Finding violations of the symmetry constraint
Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
3
votes
2answers
472 views
Data type for currency 'type' column, e.g. usd, cat, etc.
I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details:
No more than 10 different ...
3
votes
3answers
177 views
Database tables design
If I have, in my Java code, the following classes:
Patient :contains instance of anther classes (mentioned bellow)
PatientBasicInformation: contains PatientId, phoneNumber, Name, Gender, Age.
...
3
votes
2answers
88 views
Exclude dataset from a query
I have 2 tables
EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and
CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period).
Primary key for both tables are (StudentID, SubjectID, ...
3
votes
1answer
104 views
What is the right way to manage users data?
I'm trying to create a simple register login script with user profiles, I created a table with the following structure:
+------------+---------------+------+-----+---------+----------------+
| Field ...
3
votes
1answer
790 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. ...
3
votes
3answers
114 views
Is this database fine for calculation?
I am doing an application called as Invoice Application.For that I have my database is like this.
Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
3
votes
2answers
115 views
What would be the best way to model my simple table?
What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?
Or, searching for the right row through a table with only 1 column with ...
3
votes
2answers
204 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
