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
1
vote
5answers
688 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
1
vote
3answers
165 views

Make column default a query

Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
1
vote
2answers
293 views

Database of the website “eMovies”

Here is my exercise: Given database of the website "eMovies" that manage data about cinema movies. build the given database in Access and create the relationships between the tables. My attempt: ...
0
votes
0answers
105 views

Multi-Tiered Database Design Issue

I am trying to build the multi-level model as demonstrated. When a user creates a campaign they will select some categories and some metros from tables Category and Metro. Those selections will then ...
0
votes
2answers
6k views

How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference. desc urllist; +-------+---------------------+------+-----+---------+------...
0
votes
1answer
154 views

Comma List vs Multiple Records

I am currently building out an application that will contain many users, two roles and two groups. Logically we want users to be able to be administrators of other users within the two groups. User ...
0
votes
2answers
60 views

RDBMS: Proper way to store data - comma separated variables or in different fields or tables?

Fundamental question that I cannot seem to find an answer on. I have a database that stores line items from receipts along with the username and receipt number. Right now the lineItem column is just ...
0
votes
1answer
31 views

Designing SQL database with two types of users

So I'm trying to design a Postgresql database with two types of users. Customers and Brands A Customer cannot be a brand and a brand cannot be a customer. Is this the right approach? I feel I may ...
0
votes
2answers
160 views

Modelling users and groups - but with multiple types of groups

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them). However, its seems I'm dealing ...
40
votes
2answers
111k views

Database model with users, roles and rights

I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user. Below is ...
47
votes
5answers
48k views

When should you denormalize?

I think we are all familiar with database normalization. My question is: What are some guidelines that you use when you want to denormalize the tables?
46
votes
5answers
41k views

How to design a database for storing a sorted list?

I am looking to store a sorted list inside a database. I want to perform the following operations efficiently. Insert(x) - Insert record x into the table Delete(x) - Delete record x from the table ...
25
votes
4answers
14k views

Whether or not to create separate tables for different product types?

I'm in the process of designing a database and I'm having second thoughts about my initial design decisions... Product types are as follows... Models, parts, replacement part kits and options. ...
31
votes
5answers
3k views

Is it OK to keep a value which updates in a table?

We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc. Up until now we had a Card entity which has a collection of Account entity, ...
13
votes
3answers
41k views

Schema design for products with multiple variants/attributes?

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes. From all the research I've done ...
24
votes
4answers
46k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
15
votes
1answer
14k views

Understanding a notification system

I have been looking into how to build a notification system on SE and elsewhere and found myself drawn to the solution that is the accepted answer here: https://stackoverflow.com/questions/9735578/...
35
votes
3answers
19k 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?
32
votes
5answers
18k 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, ...
23
votes
3answers
22k 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 ...
43
votes
5answers
33k 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 ...
30
votes
10answers
938 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 ...
32
votes
3answers
7k 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 ...
26
votes
5answers
13k 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 ...
12
votes
2answers
13k 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, ...
31
votes
3answers
30k 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 ...
16
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 ...
14
votes
3answers
21k 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 ...
24
votes
7answers
5k 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 ...
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 ...
15
votes
1answer
7k 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
2answers
9k 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 ...
10
votes
2answers
3k views

Data Warehouse design for reporting against data for many time zones

We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) ...
22
votes
2answers
946 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 ...
14
votes
3answers
5k 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 ...
10
votes
1answer
8k 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. ...
8
votes
2answers
12k 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 ...
28
votes
1answer
21k 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 ...
21
votes
6answers
22k 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 ...
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 ...
13
votes
2answers
90k 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 ...
13
votes
2answers
3k 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 ...
20
votes
3answers
774 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
979 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 ...
18
votes
2answers
4k 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) ...
14
votes
2answers
7k 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 ...
14
votes
5answers
14k views

Sharing a single primary key sequence across a database?

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it ...
14
votes
2answers
2k views

Modelling constraints on subset aggregates?

I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and moreover, that solutions for them may inspire solutions for me, so don't consider this PostgreSQL-...
11
votes
4answers
8k 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 ...

1
3 4
5
6 7
10