Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
571
questions
12
votes
2
answers
24k
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. ...
11
votes
1
answer
36k
views
Is it "OK" to have a table that only contain foreign keys?
By "OK", I mean "it doesn't have any bad impact with the overall database design".
For example, will it cause insertion problems?
Or will it be a problem in the future when I add field in the table?...
11
votes
1
answer
6k
views
Adding nullable column to table costs more than 10 minutes
I have problems to add a new column on a table.
I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time.
ALTER TABLE mytable ...
11
votes
3
answers
10k
views
How would I track all price changes in a db in order to get the price of 'x' product on 'y' date
I would need to track a products price changes so that I can query the db for a product price at a given date. The information is used in a system that calculates historical audits so it must return ...
11
votes
3
answers
22k
views
Inventory database structure when inventory items have varying attributes
I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using ...
11
votes
3
answers
13k
views
How to avoid a cyclic dependency (circular reference) between 3 tables?
I have 3 tables:
People
Post
Likes
When I design the ER model it has a cyclic dependency:
1:N
People --------< Post
1:N
Post ----------< Likes
1:N
People -----...
9
votes
3
answers
82k
views
Database design of Room Booking system in a Hotel
Intro and System Description
I am currently designing a Database for a Room Booking system in a Hotel.
Client fills a Form / Request with following information about Room:
amount of people in room
...
9
votes
4
answers
3k
views
Strategy for concurrent group bookings?
Consider a seat booking database. There is a list of n seats, and each one has an attribute is_booked. 0 means it isn't, 1 means it is. Any higher number and there is an overbooking.
What is the ...
9
votes
2
answers
8k
views
Use composite type to create new table
In ORDBMS databases I have seen that complex types can be used:
create type name as( ...)
And I can references those types when creating a new table, like:
create table example (row_name ref(name))
...
9
votes
2
answers
4k
views
Find parent rows that have identical sets of child rows
Suppose I have a structure like this:
Recipes table
RecipeID
Name
Description
RecipeIngredients table
RecipeID
IngredientID
Quantity
UOM
The key on RecipeIngredients is (RecipeID, IngredientID).
...
8
votes
1
answer
20k
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 ...
8
votes
3
answers
15k
views
INSERT takes over 5 hours, when inserting more than specific number of rows
When inserting less then about 1,350,000 rows to the table it all takes about 2 minutes, however when number of inserted rows is bigger, then the time needed to insert data grows to about 5 hours.
...
7
votes
3
answers
2k
views
What are object-relational databases, and why is this model necessary in spatial databases?
Over in GIS SE, many of us use ESRI geodatabases. ESRI describes geodatabases as being object-relational.
What are object-relational databases, and why is it necessary to use this model in spatial ...
7
votes
3
answers
37k
views
Database design to track student attendance
I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students.
Creating a database for attendance to track their ...
7
votes
2
answers
15k
views
how to generate the creation script for partition function and partition schema?
I have a script to generate the create table script. You can see it on my answer to this question.
However, now I need to script my partition functions and partition schemes
On this question here, ...
7
votes
1
answer
6k
views
What's difference between standard view and indexed view in SQL Server [closed]
I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't?
Indexed view needs to be ...
7
votes
3
answers
17k
views
Database design for an E-commerce website
I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
7
votes
1
answer
10k
views
2NF decomposition + database normalization
If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
7
votes
1
answer
11k
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 ...
7
votes
2
answers
7k
views
How to implement soft deletes?
What is your method of implementing soft deletes in a database?
Ideally the solution would allow for
decent performance on large tables
take relationships into account
take unique keys into account
...
6
votes
3
answers
5k
views
SQL Server Primary key / clustered index design decision
Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.
I've got a product history ...
6
votes
1
answer
8k
views
Two-column foreign key constraint only when third column is NOT NULL
Given the following tables:
CREATE TABLE verified_name (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
UNIQUE (name, email)
);
CREATE ...
6
votes
1
answer
7k
views
Designing a database structure for companies and stock owners
I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the ...
6
votes
2
answers
3k
views
Better database for "keep always the 5 latest entries per ID and delete older"?
I have a PostgreSQL database with a history table where I store an fooID (not a primary key serial but a text), an attribute target and the current timestamp whenever the target for this fooID changes:...
6
votes
3
answers
481
views
Is there some drawback to define SPARSE columns?
I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?
5
votes
2
answers
1k
views
Maintaining referential integrity in a booking system
I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.
I have 3 tables (simplified for this question).
Table 1- CourseSize table- this determines the ...
5
votes
1
answer
3k
views
ERD for a web development company
I am working on an ERD which represents the relationships between entities in a Web development company behavioral model.
There I have entities like:
Customer
Service ----------- (like: web ...
5
votes
3
answers
2k
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 ...
5
votes
2
answers
4k
views
Constraint many to many table between two child tables
New to this exchange site so I hope my question isn't inappropriate.
Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently.
One table has a One-To-...
5
votes
2
answers
2k
views
Best fill factor for GUID clustering key
We have a database that uses GUIDs for most of their columns in a data (non sequential). Does anyone have any best recommendations for fill factors on a GUID database in an hgih insert/update database?...
5
votes
2
answers
2k
views
Should I create multiple tables for different entity states, statuses or stages?
I have a tasks table in my database and, in the business domain of interest, tasks can have multiple states: “open”, “begun”, “in review” and “completed”.
Despite having “open” and “begun” in the same ...
5
votes
2
answers
1k
views
When to add another lookup table for the same field
Example
Table Recipe uses a lookup table for Recipe Category;
Table Ingredient uses a lookup table for Ingredient Category;
They are in a Many-to-Many relationship;
A Recipe Category will not be used ...
5
votes
1
answer
1k
views
For alignment optimized table is bigger than original table - why?
In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
5
votes
3
answers
3k
views
Best way to model a single user with multiple usernames
I'm looking to write a website where a user will login with their email address and password, but can have multiple usernames.
The context is a social gaming website. This may result in a user ...
5
votes
2
answers
4k
views
Identity Column in Concurrency, Multithreading, Parallel Processing
Our company would like to understand the effects:
If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),
and the Application is conducting multi-threading parallel ...
4
votes
1
answer
239
views
Checking existence and syncing data with millions of rows
I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained ...
4
votes
2
answers
2k
views
How to best encapsulate monetary values in a database
I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the ...
4
votes
3
answers
1k
views
Understanding data abstraction levels
The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels.
If I understand correctly, the external level represents ...
4
votes
6
answers
4k
views
Database design for an exam
I am designing database for an exam, and I am stuck. I do not know how to do it.
Here is the relevant information:
Student answers 10 questions.
Each question has 3 options (answers) and student ...
4
votes
2
answers
3k
views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
4
votes
1
answer
2k
views
Placing a uniqueness constraint on a date range
I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).
How do I place a constraint on the table such that bookings are not ...
4
votes
2
answers
3k
views
Database normalization: Describing trait - foreign key to table, or varchar field with value?
My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order".
In my ...
4
votes
1
answer
539
views
Proper Database Partitioning
I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
3
votes
1
answer
2k
views
How to normalize my db schema for a quiz application? [duplicate]
I am putting together a quiz application. (I'm calling each quiz a "challenge")
I have a "challenge" table and a "challenge_question" table which is pretty straightforward.
challenge table:
id
...
3
votes
1
answer
10k
views
Representing a Superclass/Subclass (or Supertype-Subtype) relationship in a MySQL Workbench diagram
Situation
I am trying to figure out how to represent a one-to-one (1:1) relationship regarding a superclass/subclass or supertype-subtype structure in an entity-relationship diagram (ERD) of the kind ...
3
votes
1
answer
3k
views
Restrict two specific column values from existing at the same time
I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.
I would appreciate any help creating a constraint that will enforce this.
CREATE TABLE example
(
...
3
votes
2
answers
18k
views
Biggest possible double/float for a column in my MySQL database
I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking ...
3
votes
2
answers
2k
views
SQL order by query results in any arbitrary (but reproducible) manner
For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...
3
votes
1
answer
3k
views
What's better here - a single column or multicolumn primary key?
Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
3
votes
1
answer
180
views
Efficiently storing query-affecting settings in a database
I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).
My example
...