0
votes
0answers
11 views

Optimal layout strategies for Time-Series using Columnar storage

In the context of time-series and specifically columnar storage systems. Question: What are the processes or heuristics that are used to determine how best the columns of contiguous values be grouped ...
2
votes
1answer
81 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
0
votes
0answers
38 views

performance questions in database design

I need to describe a set of studies that are practiced to a patient, every patient have a "Clinical History", that are described in another table called "Clinical History Lines"(hclines) Every visit ...
0
votes
0answers
14 views

What is the optimal way to get a tabular view from an Entity-Attribute-Model table?

Following up on my previous question, I have created a table for storing measurements of different physical quantities. The schema looks like this: CREATE TABLE sensorreadings ( time TIMESTAMP, ...
0
votes
0answers
40 views

Database design for large incoming data

I've been tasked to design a database to handle a large volume of incoming traffic from sensor readings, possibly 1,000s an hour when it's at full strength. Each reading will be relatively short - ...
1
vote
1answer
64 views

Handling a chat room in a database

I have been searching all day to find a better answer to my question but those I found was useful but do not critically analyse my question. I am building a chat room using php, mysql, jquery and ...
1
vote
2answers
116 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
0
votes
2answers
60 views

Are #tables and #procedures 'not best practice' or 'bad practice'?

I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files. In the file, temp procedures and temp tables are used quite heavily ...
0
votes
1answer
29 views

Need DDL for an Oracle Table that would be 1 row = 1 8K Block

For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature ...
0
votes
2answers
32 views

Do I need foreign keys in ORMs?

Object Relational Mapping (take ActiveRecord and Rails, for example) allows a programmer to define relations between models, and callbacks on delete. So my question is: do I still need to define ...
1
vote
2answers
82 views

Table design for multi-type data

The information model I am modelling from includes an "any type". For example a customer can have zero or more 'entries' each of which can have a number of values. Each value could be a either date, ...
1
vote
1answer
126 views

Slow delete caused by many foreign keys

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the ...
1
vote
0answers
20 views

What is the best type of database for numerous multi-threaded access? [closed]

I am developing a simulation and have limited experience working with databases (very limited SQLite3 exposure). Here is my task: Server that stores a potentially large database (no size requirement ...
1
vote
2answers
122 views

Oracle 11.2 using too few memory

it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012). The problem is that with more than doubled cpu power, ...
0
votes
1answer
53 views

Is array serialization always bad idea?

I'm making form-generating app in php. I have Widget table with type_id (html input) field, and I would like to add options field, like width/height, step, range, etc. Different widgets do have ...
0
votes
1answer
36 views

Is it OK to duplicate parent field in child table for performance?

I have a very complicated query which involve more than 10 tables. Some parent tables are loaded only to search on one field. I wonder if I can copy this field in the child table (this data is never ...
6
votes
1answer
418 views

Database design for handling 1 billion rows and counting

We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes ...
0
votes
0answers
35 views

more queries vs storing results in database

Environment: Embedded (smartphones) Data Structures: Object Table A: id, field1, field2... Object Table B: id, field1, field2... Object Table C: id, field1, ... , Object A, Object B ... many ...
0
votes
0answers
54 views

Efficient way to fetch/update X records in a loop

What I need is a simple process that handles requests. I have setup a simple table as follows: CREATE TABLE Requests ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, request_param VARCHAR(250) ...
0
votes
4answers
100 views

T-SQL: Complete Database in one table

at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could ...
1
vote
1answer
142 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
0
votes
0answers
46 views

how to design my database for faster reads for my situation

I have a situation where i have two categories. Lets say type A and type B. In my database I have some 50,000 type A entries and 100,000 type B entries as of now. For each entry mentioned above(type ...
0
votes
1answer
134 views

Entity with >150 attributes: One table or many tables with relationships

I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY ...
1
vote
1answer
1k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
1
vote
2answers
133 views

Splitting up tables with similar structures since they contain unrelated data? (huge numbers)

Is my reasoning correct in splitting up tables with similar structures since they contain unrelated data? I'll explain the situation with an example : Suppose there is a server which hosts an online ...
5
votes
2answers
383 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
1
vote
2answers
569 views

boolean purpose column char(0) or tinyint(1)

For Boolean purpose column which one is better: nullable char(0) or tinyint(1). I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said: "If ...
0
votes
1answer
103 views

Am I wrong in table design or wrong in selected index when made the table?

I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
1answer
89 views

When is it worth it to use Triggers and stored procedures/functions? [closed]

As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post ...
4
votes
1answer
251 views

Where and how to store my weird datamart

I could really use some help here. Here's my situation. I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
1
vote
2answers
281 views

How should I store multiple relationships (many posts to many categories)?

I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ? I thought of it this way first: ...
4
votes
2answers
1k views

Why use separate databases for high traffic/volume tables?

While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things. The first one contains the ...
2
votes
2answers
300 views

How to implement posts with “seen by” like facebook?

Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me. And if I see a specific post inside a group thought a url ...
5
votes
1answer
869 views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
5
votes
1answer
2k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
3
votes
1answer
62 views

Does avg_query for PgBouncer include update and inserts?

Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage: avg_query Average query duration in ...
6
votes
1answer
148 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
7
votes
3answers
880 views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
2
votes
1answer
455 views

How should I model a binary-tree like data using Adjacency List?

I'm making a system where a user can recruit others, resulting in a binary tree model of users. I have decided to use Adjacency List to model the data but I have some doubts about the number of fields ...
4
votes
3answers
560 views

Is a surrogate key better than a natural key in this case

I copied this code from here: CREATE TABLE records( email TEXT REFERENCES users(email), lat DECIMAL, lon DECIMAL, depth TEXT, upload_date TIMESTAMP, comment TEXT, PRIMARY ...
3
votes
1answer
201 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 ...
2
votes
1answer
174 views

Deriving formulas for input/output

I'm currently enrolled in a DBS class and am having problem with an assignment. I've searched around and have been unable to understand what it is I'm meant to be doing with this derivation formula. ...
3
votes
2answers
158 views

Tablestructure for fast inserts/deletes with foreign keys

Current Situation We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product. Example: id | competitors | some infos about competitor | ...
4
votes
1answer
188 views

250 billion rows - computing interconnectedness on a really huge scale

I have a table of approximately 700k rows, each identified by a unique itemnumber. Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
3
votes
1answer
112 views

which design better when use foreign key instead of a string to store a list of id [duplicate]

Possible Duplicate: Is using multiple foreign keys seperated by commas wrong, and if so, why? I'm building online examination system. I have designed to table, Question and GeneralExam. The ...
1
vote
2answers
111 views

How to store massive quantities of machine generated data?

I am tasked with developing a system that stores massive quantities of machine generated data (syslogs) from a number of server farms and I am wondering what tools you fine folks use such instances. ...
18
votes
2answers
3k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
1
vote
1answer
377 views

Database design with different user roles connected by different relationships to one organizaiton table

An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee. One employee can only be a part of one organization and if he is an employee then he can't be ...
2
votes
2answers
615 views

Closed loops in database model, does it affect the performance?

I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the ...
3
votes
2answers
1k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...