Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.
4
votes
1answer
99 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 ...
4
votes
1answer
69 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
4
votes
2answers
86 views
How to properly configure PostgreSQL RAM usage?
Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
4
votes
3answers
148 views
Storing date increments in tables
I'm working on a ASP/MS SQL project that performs a lot of calculations based on dates and time. In one case, the system will lookup a value based on the nearest half hour increment throughout the ...
4
votes
1answer
132 views
Writing a database analysis document
I've been tasked with writing an analysis of a database that is currently used on a small scale. The intent of the document is to show to the business (as well as technically) that the database can ...
4
votes
2answers
857 views
Allowing Foreign Key to be Null
I'm trying to create a relationship between two tables where one column in TABLE A is the foreign key for a column in TABLE B. However, there is one row in TABLE B that is currently null in that ...
4
votes
1answer
311 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
4answers
760 views
Primary key should be chosen so as attributes never change?
I was reading about keys in this DatabaseSystems book -
The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person ...
4
votes
1answer
107 views
How can I improve this design?
I'm creating an event planning PHP/MySQL web application as a learning project. The whole app revolves around the idea of the event and its various components (participants, groups, lists, etc.)
I ...
4
votes
1answer
129 views
Use multiple columns for location or supertype/subtype?
Given an Equipment entity, it has a location, generically called Whereabouts. This could be a reference to:
a User entity (the employee who will use and be responsible for the eqipment for the next ...
4
votes
1answer
218 views
Dimension Help - Deciding Fact or Dimension
We have the following Dim and Facts:
Customer Dim: SCD Type 2, Info about the customer ie first purchase date, name, address, etc
Product Dim: SCD Type 2, about our products
Customer Snapshot ...
4
votes
2answers
82 views
Design for storing data only applicable to a small subset of records
So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients.
Basically, we have a bunch of tables in some format similar to this (in ...
4
votes
1answer
516 views
Schema designing for handling multiple payment gateways
This is more of a question that requires feedback.
I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the ...
4
votes
2answers
333 views
How to design a schema to store search keywords
I am doing a database design that stores search keywords of a search form, used to generate the most popular search keyword reports by date, month and year. I have made a simple schema:
CREATE TABLE ...
4
votes
1answer
124 views
Enforce data integrity with a recursive table
I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
4
votes
1answer
67 views
Adding an ID column to a join table (many-to-many) so that it can be targeted
I'm building a web application for a company that sells cars. They buy leads (personal details of people interested in buying cars) and try to turn them into customers. The company has a number of ...
4
votes
1answer
176 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
277 views
SQLite3 simple database design question
I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database.
The ...
4
votes
1answer
128 views
How to set constraints on UDT in SQL Server 2005 - 2012
I'm creating my UDT:
CREATE TYPE [dbo].[Code]
FROM VARCHAR(20) NOT NULL
As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that ...
4
votes
1answer
198 views
Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?
I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data.
I already tried everything I found in ...
4
votes
2answers
537 views
Using IDs from multiple tables in a single column
One of my co-workers created a schema similar to the following. This is a simplified schema including only the parts necessary to address this question.
The system rules are as follows:
...
4
votes
0answers
70 views
Does PgBouncer create a separate pool for every database config entry?
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this.
For example, ...
4
votes
1answer
95 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
4
votes
1answer
129 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
114 views
MI Data Warehouse Advice
I have recently started a new job and part of my remit is to try to rescue the Management Information (MI) Data Warehouse. I use the term Data Warehouse very loosely here!
The server setup is:
...
4
votes
1answer
477 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 ...
4
votes
1answer
294 views
Temporal database design, with a twist (live vs draft rows)
Not sure if/how I should be cross-posting this... But in case some follow DBA but not SO...
Is there a generally accepted means of managing live vs non-live rows in a row-version controlled ...
4
votes
1answer
154 views
SQL Server database design for “archived but available” data
We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice.
...
3
votes
2answers
2k views
Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?
I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
3
votes
3answers
316 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
2answers
777 views
Having a unique ID for 2 different tables
We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'.
The ID is therefore unique per both tables ...
3
votes
4answers
166 views
PRIMARY KEY CONSTRAINT fundamentals
Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ?
The RDBMS that is used is SQL Server.
In fact, ...
3
votes
4answers
424 views
Should we add extra 5 columns or build a separate table?
Currently we have a table with 35 fields in it.
Now, we got a requirement to add 5 extra columns but these columns will not be fill always.
So, I was thinking just add one column and have that id as ...
3
votes
3answers
486 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
167 views
What are the advantages of a database design with single table for MtM relationships?
The database that backs our software product has a table design like the following:
Positions
ID Name Parts
1 One 12345
2 Two 12346
3 Three 12347
Collections
ID ...
3
votes
5answers
8k views
How to export an ER diagram from an Oracle DB?
I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...
3
votes
3answers
985 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
263 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
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
1answer
287 views
Surrogate key / Primary key: Better to use an existing unique data field or create a key field?
I am not sure if this question has been asked or not. At least I couldn't find it.
I am curious about a primary key in terms of efficiency with data searching and retrieval.
This is a hypothetical ...
3
votes
2answers
490 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
150 views
Storing Phone Numbers - Proper design
First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career. One of the things I remember (maybe incorrectly) is that when ...
3
votes
2answers
278 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 ...
3
votes
2answers
121 views
Which tables to create according to relations
We are planning a new system with a new DB (MS SQL Server).
We have the following entities and the following relations:
Entities:
Clubs,
Chains,
PaymentTypes,
etc...
Relations:
Clubs-Chains (M-M);
...
3
votes
3answers
181 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
3answers
179 views
How to prevent redundant relationships in data?
If I want to link users with other users. Would this be a good way to do it:
I'm asking because I probably get double data like this if i don't do specific checks:
+------+-------------+
...
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
4answers
2k views
Examples of SQL transaction procedures for sales tracking or a financial database
I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions:
salesman creates new product ...
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 ...
3
votes
1answer
827 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. ...
