The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

3
votes
1answer
691 views

Do I have duplicate key indexes?

CREATE TABLE relations ( object_id BIGINT(20) NOT NULL PRIMARY KEY, object_term BIGINT(20) NOT NULL, UNIQUE KEY link(object_id, object_term), CONSTRAINT fk_term FOREIGN KEY(object_term) ...
3
votes
2answers
115 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 ...
3
votes
1answer
131 views

Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get ...
3
votes
2answers
1k views

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
2
votes
1answer
4k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
0
votes
2answers
177 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 ...
52
votes
9answers
6k 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 ...
36
votes
6answers
26k views

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

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....
26
votes
3answers
4k 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 ...
16
votes
4answers
16k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
19
votes
3answers
3k 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 ...
13
votes
2answers
1k 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 ...
8
votes
2answers
7k 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. ...
6
votes
3answers
3k 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 ...
7
votes
2answers
2k 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 ...
22
votes
5answers
16k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
8
votes
2answers
3k 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 ...
6
votes
4answers
441 views

Don't know how to transform variable entity into relational table

INTRODUCTION AND RELEVANT INFORMATION: The following example illustrates the problem I face: Animal has a race, which can be a cat or a dog. Cat can be either Siamese or Persian. Dog can be a German ...
5
votes
3answers
14k views

How to model inheritance of two tables mysql

I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal). ...
22
votes
8answers
10k 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 ...
17
votes
5answers
3k views

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like: CREATE TABLE Book ( BookID INT NOT NULL , BookTitle ...
9
votes
5answers
11k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
8
votes
1answer
583 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
1answer
733 views

Concept of Schema in PostgreSQL

I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design. Why should I use it? Can it affect me in future if I decide to not think ...
3
votes
4answers
359 views

Normalization for postal addresses in US (address, county, city, state, zipcode)?

I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get ...
3
votes
2answers
6k 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 ...
3
votes
4answers
11k views

Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored ...
2
votes
1answer
3k views

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
0
votes
2answers
3k 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 ...
15
votes
5answers
14k 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 ...
2
votes
1answer
293 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 ...
2
votes
2answers
1k 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 ...
2
votes
1answer
530 views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...
1
vote
3answers
462 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 ...
1
vote
1answer
2k views

Many To Many Relationships

I am working on a conceptual model for a database. I have the following entities: Student (id, name, date of birth, department) Subject (id, name) This is a many-to-many relationship, one student ...
1
vote
1answer
6k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
0
votes
1answer
2k views

Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

I'm working on a question/answer project and having a hard time coming up with a suitable database structure. Each question can have from 1 - "n" answers, and each answer is associated with one of ...
-2
votes
1answer
273 views

How to represent it with UNIQUE indexes and foreign keys?

"Group" is a group of pupils. "Topic" is a topic of lessons. To every group corresponds exactly one topic. (So there may be multiple groups per topic.) To a pupil corresponds multiple groups, but ...
11
votes
2answers
2k 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
3answers
411 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
6
votes
2answers
2k views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
6
votes
2answers
2k 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 ...
5
votes
2answers
767 views

Introducing an 'age' category in a patients database

I want to do the following in my DB related to patients: when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients how ...
5
votes
2answers
236 views

Web Database - How to store user information

I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a ...
5
votes
1answer
3k 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 ...
4
votes
1answer
2k views

How to relate two rows in the same table

I have a table where the rows can be related to each other, and logically, the relationship goes both ways (basically, is directionless) between the two rows. (And if you're wondering, yes, this ...
4
votes
1answer
361 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
2answers
460 views

Best design for a tree-like structure database

I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches. Each batch can be split (aliquoted) practically indefinitely. That ...
3
votes
2answers
385 views

Advice on a basic design, first time database design

I am taking a course to become a Java developer. The course involves using databases but we never really design any unfortunately. Most of the time we get pre-made databases and we have to implement ...
3
votes
2answers
991 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 ...