Tagged Questions
0
votes
1answer
49 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 ...
2
votes
0answers
85 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 ...
0
votes
1answer
41 views
MySQL DB Design
I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, ...
1
vote
2answers
55 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:
...
3
votes
2answers
127 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
85 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 ...
4
votes
1answer
88 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 ...
3
votes
1answer
73 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
0answers
23 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
84 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 ...
6
votes
3answers
521 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
0answers
132 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 ...
3
votes
3answers
311 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
105 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 ...
1
vote
1answer
100 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
135 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
128 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
87 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 ...
0
votes
0answers
47 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. ...
7
votes
2answers
520 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
120 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
214 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
261 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 ...
2
votes
1answer
85 views
Help with database design
I'm quite the beginner when it comes to databases, so please bear that in mind.
I need to store user statistics in several categories for an application.
In the near future I will most likely need ...
3
votes
2answers
295 views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
2
votes
2answers
177 views
Writing to a DKNF database, but reading from a de-normalized table for performance?
Would the following approach be reasonable?
The Goal: queries have to be relatively fast, but without the hassles incurred by writing to a de-normalized database.
The Context: A database for ...
2
votes
5answers
188 views
How to shard 14 gb of data for archival database?
We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state. We now want to store this in a database. There would be ~130 million records.
The ...
5
votes
1answer
275 views
Denormalized table designs in normalized database?
There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
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 ...
6
votes
1answer
538 views
What should every DBA know? [closed]
As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
2
votes
4answers
281 views
One table with many rows or many tables with a few rows
I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at ...
0
votes
3answers
558 views
Will using bigint vs mediumint have a performance impact?
I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I ...
3
votes
2answers
253 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
2
votes
1answer
162 views
Need advice for a 'tag' table design
My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design:
2 tables option : 1 table for the ...
2
votes
1answer
85 views
Group_by and having vs Joins when table is self joined 5-6 times
Now the situation due to which this question came to me.
Schema is
Table User_Read_Book
user_id | book_id
Now I want to get users who have read certain books. Say give me users who ...
2
votes
1answer
417 views
PostgreSQL versus MySQL for EAV structures storage
Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
2
votes
1answer
66 views
Single field instead of several fields
There is a relational database with the following Users table
User
Id
Name
Age
Parents
Friends
Colleagues
...
And there are tables Parents, Friends, Colleauges, etc. Due to specifics of database ...
2
votes
1answer
135 views
Oracle ash report
Recently, i have been handed over with an ash report from DBA.
To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
3answers
229 views
Express union which is dependent on other results
I would like to make a union of two tables that are actually in a hierarchical constellation.
How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship.
...
1
vote
1answer
62 views
Reading and writing the common data
I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application ...
9
votes
2answers
222 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
7
votes
1answer
280 views
Could Stack Overflow run on SQL Server Web Edition?
I am starting a new web venture that may need to scale to a high number of users.
I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in ...
0
votes
3answers
328 views
How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?
I want to design a db schema concerning a schedule.
I. I can use all fields in the same table, for example:
id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su
1 | 1 | ...
2
votes
1answer
252 views
Pros and Cons of having additional columns in join table of many to many relationship
I searched but did not find any discussions on this topic.
Are there any pros and cons on creating many extra columns on joined table?
Does it affect speed of data retrieval because we are joining 3 ...
0
votes
1answer
230 views
How could I handle heavy load and consistency for product inventory in online retailers? [closed]
I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue).
Hope you guys don't mind.
...
4
votes
2answers
684 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, ...
11
votes
2answers
2k views
Is nested view a good database design?
I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
2
votes
1answer
104 views
Does document “depth” affect CouchDB performance?
I have a project where it is very tempting to use a deeply nested collection of data. But how does that affect performance? Are there any reasons why I should avoid doing this? I'm talking about lists ...
11
votes
7answers
173 views
Which of these table designs is better for performance?
I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this.
Here's what I know
Company ...
3
votes
3answers
809 views
Suggestion required for Large Database Optimization and performance
We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
