Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
22
votes
4answers
40k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
32
votes
5answers
17k views

What database technologies do big search engines use? [closed]

Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data? What sort of database or technologies do they employ for this? It takes few milliseconds, ...
32
votes
3answers
15k views

How do I know what indexes to create for a table?

Is there a way I can figure out the best way to know which indexes to create for a table?
30
votes
10answers
844 views

What is an effective way of labeling columns in a database?

I used to label columns in my databases like this: user_id user_name user_password_hash To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped ...
20
votes
2answers
21k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the ...
42
votes
5answers
28k 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 ...
15
votes
1answer
3k views

Choice of authentication approach for financial app on PostgreSQL

First some background. The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL. We implement a very large amount of business logic in user defined ...
30
votes
3answers
6k views

Duplicate column for faster queries?

The title doesn't make too much sense, but I couldn't think a better title for this problem. I have the following tables Projects id name Customers id id_project name Payments id id_customer ...
24
votes
7answers
15k 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 ...
23
votes
5answers
12k views

What are some ways to implement a many-to-many relationship in a data warehouse?

The dominant topologies of Data Warehouse modelling (Star, Snowflake) are designed with one-to-many relationships in mind. Query readability, performance, and structure degrades severely when faced ...
8
votes
2answers
11k views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
24
votes
7answers
4k views

What problems are solved by splitting street addresses into individual columns?

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the ...
27
votes
3answers
23k views

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of ...
18
votes
6answers
3k views

What can be the downside of always having a single integer column as primary key?

Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM. However, in order to have a simple design for the ...
13
votes
3answers
19k views

Is there a standard implementation of a Users Database?

I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
12
votes
2answers
8k views

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
22
votes
2answers
799 views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
8
votes
2answers
11k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
7
votes
1answer
1k views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
20
votes
4answers
7k views

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
19
votes
4answers
13k views

SQL Server database on an SSD - any advantage to a separate file for every table?

I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
18
votes
6answers
12k views

Preferred way to store DateTime

We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information? Storing Date and Time in 2 separate columns or one column using DateTime? Can ...
13
votes
1answer
6k views

Uniqueness constraint with date range

Consider a prices table with these columns: id integer primary key product_id integer -- foreign key start_date date not null end_date date not null quantity integer price numeric I'...
13
votes
3answers
4k views

Foreign keys - link using surrogate or natural key?

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
13
votes
2answers
2k views

Design Pattern - One of Many Parent Tables

I come across a situation in database quite frequently where a given table can FK to one of a number of different parent tables. I've seen two solutions for the problem, but neither is personally ...
12
votes
2answers
81k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
20
votes
3answers
636 views

Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance

I'm in the process of designing a new system for a large geospatial data set that will require rapid read query performance. Therefore I want to see if anyone thinks it is possible or has experience/...
19
votes
4answers
838 views

Entity relationship problem

I have 4 tables related like this (it's an example): Company: ID Name CNPJ Department: ID Name Code ID_Company Classification: ID Name Code ID_Company Workers: Id Name Code ID_Classification ...
14
votes
2answers
6k views

Storing massive amounts of data from a sensor array

I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to ...
13
votes
2answers
2k views

Modelling constraints on subset aggregates?

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
10
votes
1answer
4k views

Best practice for storing record metadata

What is the best practice for storing metadata of individual records in a database? I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
9
votes
4answers
6k views

Proper way to store a value that could be multiple different types

I have an Answers table and a Questions table. The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what ...
8
votes
1answer
1k views

Redesign the storage of large amounts of sensor data

I have been tasked to implement/redesign a solution that will store weather data from a sensor array. The array will consist of ~40 towers, each with about ~10 sensors each that'll sample atmospheric ...
7
votes
2answers
3k 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 dimensions ...
7
votes
4answers
14k views

Database design for a time tracking application

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he ...
4
votes
1answer
5k views

Multiple User Types - DB Design Advice

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. ...
3
votes
1answer
3k views

Database Design Cars-Models / Car Classifieds

I'm developing a database for Car Classifieds. I'm not sure how is the best way to design it. There are several features of cars like if it has 2 or 4 doors, if it is 4x4 or 4x2, engine capacity of 1....
23
votes
1answer
15k 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 ...
15
votes
2answers
2k views

Non-integer primary key considerations

Context I'm designing a database (on PostgreSQL 9.6) which will store data from a distributed application. Due to the application's distributed nature, I can not use auto-increment integers (SERIAL) ...
10
votes
1answer
1k views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
8
votes
1answer
19k views

Ternary relationship: what's the difference between having single table and having multiple tables?

Consider the following ternary relationship: Suppose that all entities have only two attributes (PK and Name). Here are the tables I derived (5 tables): Sector ------------------------- ID_Sector ...
7
votes
2answers
73k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
6
votes
1answer
3k views

How to keep long multiple of questions and answers in database?

I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for ...
5
votes
2answers
2k views

Schema for Financial Database

My company is starting a new initiative aimed at building a financial database from scratch. We would be using it in these ways: Time series analysis of: a company's financial data (ex: IBM's total ...
3
votes
2answers
23k views

How to make a trigger that will compare input with a value of other table?

I had made two tables with the following fields: TABLE 1: "Bookings" FIELD: "fk_flight_number" FIELD: "date_of_reservation" TABLE 2: "Flights" FIELD: "flight_number" FIELD: "...
3
votes
3answers
528 views

Database design for changing number of fields

Google Contacts lets you to enter more than one phone number, or email address for a contact. You can add as many as you want. I don't know if there is a limit, other than practical list size. To ...
3
votes
3answers
21k views

Designing a Products database: One Products table or many separated by vendor?

In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database. My data is in spreadsheets because it was (somewhat) ...
12
votes
3answers
6k views

What are negative keys used for?

Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet. When and why is it useful to have negative (or rather signed) ...
12
votes
2answers
3k views

Proper technique for storing users event data

I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry ...
12
votes
2answers
5k views

How to design this database to avoid cyclic dependency?

There are two tables: User Address User contains a reference to Address. Address contains the columns CreatedBy and ModifiedBy, which is reference to User. How do I design this database to avoid a ...