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
0
votes
2answers
414 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
44
votes
6answers
60k views

Best practices on common person fields (Name, email, address, gender etc…) [closed]

What are the most common best practices on length and data type on common fields like: First Name Last Name Address Email Sex State City Country Phone Number etc....
57
votes
9answers
10k views

Should you design the database before the application code is written?

What is the easiest and most efficient way to design a database? From my perspective, there are a couple of options for an application's data store design: Design the database as best as you can ...
23
votes
6answers
9k views

Needing A Database Design Book [closed]

I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
30
votes
3answers
6k views

At what point does one database per client become unfeasible?

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system. However, we're developing a new system that ...
8
votes
2answers
15k 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. ...
38
votes
3answers
13k views

Database Design: New Table versus New Columns

(This was suggested to be be repost here from StackOverflow) Currently have a table .. and need to start adding new data columns to it. Not every record (even going forward with new data after adding ...
35
votes
5answers
42k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
19
votes
1answer
20k views

Database design: Two 1 to many relationships to the same table

I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and ...
14
votes
2answers
35k views

Best way to design a database and table to keep records of changes?

I need to setup a history feature on a project to keep track of prior changes. Let's say I have two tables right now: NOTES TABLE (id, userid, submissionid, message) SUBMISSIONS TABLE (id, name, ...
10
votes
3answers
17k 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 ...
12
votes
2answers
11k 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. ...
7
votes
2answers
16k 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 ...
9
votes
2answers
18k views

composite primary keys is a bad practice? [closed]

I want to know if composite primary keys is a bad practice and if not, on which scenarios is recommendable to use. My question is based on this article The part about composite primary keys: Bad ...
7
votes
2answers
34k 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 ...
15
votes
2answers
2k views

Schema-less/flexible + ACID Database?

I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be ...
19
votes
6answers
16k views

Oracle Creating ER Diagrams and Data Dictionaries

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
11
votes
4answers
11k views

Not using foreign key constraints in real practice. Is it OK? [duplicate]

Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables. According to my senior, in real practice, those are very time ...
10
votes
4answers
29k views

Calculate row size and max row size for a table

Problem: Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate ...
7
votes
3answers
1k 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
1answer
9k 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 ...
6
votes
3answers
4k 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 ...
19
votes
3answers
15k views

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
16
votes
1answer
3k views

Designing a database for a video game business domain with multiple many-to-many relationships

I am relatively new to database design, and I decided to make my own hypothetical database for practice. However, I am having trouble modeling and normalizing it, as I esteem that there are numerous ...
16
votes
2answers
9k views

Implementation of a many-to-many relationship with total participation constraints in SQL

How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram? As it is shown, every A entity type occurrence must be related to at least one B counterpart (...
8
votes
3answers
2k 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 ...
6
votes
2answers
1k 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:...
3
votes
3answers
897 views

Can't convert this type of ER diagram into SQL or relational tables

I have stumbled upon a problem during database design. Let me explain what it is all about: Contract has some simple attributes that describe him, and may have annexes and dynamics of payment. ...
26
votes
7answers
14k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
10
votes
4answers
2k views

Does the order of columns in a columnstore index matter?

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table. Will there be any change in performance based on the order of columns that I use ...
10
votes
3answers
948 views

Integrity constraints in a relational database - should we overlook them?

I’m in a permanent discussion with the developers of the company where I work because they say it is better to get rid of relationship enforcement (via FOREIGN KEY constraint definitions) in a ...
10
votes
1answer
3k 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 ...
8
votes
4answers
757 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 ...
6
votes
2answers
3k 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)) ...
4
votes
2answers
2k 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
2answers
2k views

Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.) Instead of having Boolean columns, I could have a single array ...
3
votes
2answers
6k 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, ...
2
votes
1answer
2k views

How to remove data redundancy in product table if multiple suppliers are there for same product

I am designing a database where I have different products and suppliers. For example, I have a product, "mobile phone", say an Apple 5S. This will be sold through my website from different suppliers ...
2
votes
3answers
3k views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
2
votes
1answer
1k 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 ...
1
vote
2answers
4k views

help in multi-language medical database?

My project is to design a medical database for many countries. This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language. I started my ...
0
votes
1answer
127 views

How to store a version sequence per product

I need to create a table like the following one: +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 |...
19
votes
2answers
5k views

INT or CHAR for a Type Field

What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema: create table Car ( Name varchar(100) not null, Description varchar(100) not ...
9
votes
2answers
3k 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). ...
9
votes
1answer
3k views

Does creating a unique constraint on a Postgres column remove the need to index it?

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.
7
votes
2answers
6k 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
3answers
24k 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 ...
5
votes
1answer
970 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
2answers
236 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
1answer
1k 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 ...