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
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. ...
jmbertucci's user avatar
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?...
jsp0407's user avatar
  • 125
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 ...
Matthieu Verrecchia's user avatar
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 ...
Gunnar Norred's user avatar
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 ...
TheSecretSquad's user avatar
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 -----...
Ragu's user avatar
  • 211
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 ...
rand0rn's user avatar
  • 193
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 ...
Benjamin Scherer's user avatar
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)) ...
lcjury's user avatar
  • 257
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). ...
poke's user avatar
  • 527
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 ...
feelthhis's user avatar
  • 265
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. ...
Piotr's user avatar
  • 83
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 ...
User1974's user avatar
  • 1,457
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 ...
xan's user avatar
  • 171
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, ...
Marcello Miorelli's user avatar
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 ...
user8365's user avatar
  • 441
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 ...
Rathish Kumar B's user avatar
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 ...
cpowel2's user avatar
  • 183
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 ...
Josh's user avatar
  • 171
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 ...
Neil McGuigan's user avatar
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 ...
user avatar
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 ...
Jim Stewart's user avatar
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 ...
Jane Doe's user avatar
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:...
lathspell's user avatar
  • 163
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?
jrara's user avatar
  • 5,333
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 ...
Uncle's user avatar
  • 51
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 ...
WordPress Speed's user avatar
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 ...
Songo's user avatar
  • 293
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-...
Ingó Vals's user avatar
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?...
James Rhoat's user avatar
  • 1,547
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 ...
Mackey18's user avatar
  • 153
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 ...
Tiago Matos's user avatar
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 ...
tomka's user avatar
  • 937
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 ...
Andy's user avatar
  • 151
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 ...
user avatar
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 ...
Ehs4n's user avatar
  • 211
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 ...
Mark's user avatar
  • 141
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 ...
user2804064's user avatar
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 ...
AlwaysLearningNewStuff's user avatar
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 ...
Ray's user avatar
  • 143
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 ...
srip's user avatar
  • 143
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 ...
Sebastian's user avatar
  • 143
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 ...
CenterOrbit's user avatar
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 ...
Renkai's user avatar
  • 31
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 ...
Matchoo's user avatar
  • 33
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 ( ...
Nathan Lippi's user avatar
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 ...
idelara's user avatar
  • 133
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 ...
Marcus Junius Brutus's user avatar
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 ...
JohnCand's user avatar
  • 515
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 ...
BusinessAlchemist's user avatar

1 2 3
4
5
12