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

learn more… | top users | synonyms (4)

5
votes
1answer
16k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
5
votes
2answers
5k views

Does order matter for composite primary key in Oracle or any database?

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order ...
5
votes
4answers
2k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
5
votes
4answers
4k views

Are there any free online collaboration database schema tools? [closed]

I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
5
votes
3answers
2k views

Scalable table structure for periodically updated stats that get aggregated over time

I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later. For example, every 30 seconds I could receive the number of oranges sold at a store in the ...
5
votes
2answers
149 views

Trigger to create symmetrical rows

I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. ...
5
votes
2answers
453 views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
5
votes
1answer
348 views

Database structure, relational or data warehouse?

Good morning, Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns: Date ...
5
votes
2answers
29k 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 ...
5
votes
2answers
3k views

What Is The Point of a Primary Key?

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than ...
5
votes
1answer
10k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
5
votes
2answers
633 views

Table contains multiple names for same company

I have a table of monetary transactions that has a field for who the money was paid to or received from. Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, ...
5
votes
2answers
460 views

Relation between first name and last name

I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model) I've read that the relation is of type: 1-1: They are linked ...
5
votes
2answers
129 views

Which to store: miles or kilometers?

My app is launching to a US audience first, but I want to internationalize the database schema for future expansion. Obviously I will convert between the two units as necessary regardless, but are ...
5
votes
3answers
198 views

Why would I need a third table in a one-to-many relationship?

Good day, Our db professor at the univeristy always said that one-to-many relationships with (0, M) in one side MUST have a third table to relate them. I did not ask him back then and now I cannot, ...
5
votes
3answers
1k views

Are there any tools to go from ERD to DB design and vice versa? [closed]

I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa. For the record, the database i'm using is MySQL.
5
votes
3answers
752 views

how do I model this in database?

I am using mysql as my backend database. Here is the use case; A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
5
votes
1answer
696 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
5
votes
2answers
2k views

SQL Server database forum design sample

I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
5
votes
3answers
3k views

When to apply normalization in database design

Hello database administrators. Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior ...
5
votes
3answers
145 views

Tables with FK's of each other

I'm working on a database that stores address information. (Related to this question I posted) I have 2 tables: CREATE TABLE [dbo].[Country] ( [Code] CHAR (2) NOT NULL, [Code3] ...
5
votes
1answer
249 views

How to model complex systems like networks in Postgres

I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented ...
5
votes
1answer
279 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
1answer
547 views

Best way to index a table with two datetime columns that are equally queried

Sql Server 2008 R2. I have a table with ~70m records, about 10 inserts per second. It's currently clustered on a CreatedAt datetime column, which always increases. 50% of queries involve this ...
5
votes
2answers
3k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is ...
5
votes
3answers
828 views

What are the pros and cons of using my customer codes as a primary key?

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
5
votes
2answers
2k views

Creating partitions on a production database

I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals. I have two tables that involve a ...
5
votes
2answers
12k views

Relational Design - Multiple tables into one foreign key column?

Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was. But, the different types of meat are unique in some way (such as USDA ...
5
votes
3answers
703 views

Embedding vs foreign table in one-to-one relations?

I'm currently taking a university course about databases. Consider the following modeling scenario where a car exists only in the context of its owning student: Student (0,1) <---> (1,1) Car ...
5
votes
1answer
73 views

Multi-Datacenter design for SQL Server

I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different ...
5
votes
1answer
777 views

Denormalized table designs in normalized database?

There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
5
votes
2answers
274 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
5
votes
2answers
440 views

Best design for adding a folder/file relationship

I was given a database that has Projects and Media_files. They were in a simple 1 to many relationship. I now need to add Folders to Projects. Media_files can be placed in Folders, and Folders ...
5
votes
1answer
110 views

Hard delete vs soft delete performance

I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. ...
5
votes
1answer
175 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
5
votes
3answers
7k views

What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? [duplicate]

Possible Duplicate: Files - in the database or not? I've been reading "Programming Entity Framework Code First" ...
5
votes
3answers
9k views

Correct way to design a shopping cart websites database

I am in a web applications course. My instructor really didn't like the layout of my database, saying it was incorrect in pretty much every way. Fast forward 2 months when I'm remaking the website in ...
5
votes
1answer
560 views

How should I model a worflow of status/activities ? (rules like “if is 3 go to 4, if is 4 go to 5” etc)

I'm trying to model a workflow (status and activities) to create "rules". Let me give you an example. The main "object" let's say are "vehicle requirements", I will have this table: idRequirement, ...
5
votes
2answers
724 views

How should I best design the tables and relationships, given the following rules?

In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
5
votes
1answer
581 views

Should I Link a Fact to Hierarchical Dimension at ALL Levels or only the most granular?

Related question about the same project here. I'm looking at two approaches to model a hierarchical relationship between my fact and dimension tables in a data warehouse for storing IT infrastructure ...
5
votes
2answers
237 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
2answers
479 views

Help with good RDBMS transactional schema design that mimicks a sports league

I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are: Well normalized Transactional Application ...
5
votes
2answers
491 views

What is the primary key on a temporal table?

If I use the following table design to capture history...: CREATE TABLE MyTable ( insertion_timestamp TIMESTAMP, deleted_flag BOOLEAN, natural_key INT, attribute VARCHAR ); ... then ...
5
votes
2answers
2k views

What type of datastore should I use for high volume time series data?

Imagine a project which requires to be able to process 50,000+ (tiny ints) writes per second. The data, once inserted, won't change. The data is a measurement on a particular time. Sub second ...
5
votes
2answers
245 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
5
votes
1answer
569 views

Would appreciate some suggestions on how I should model this data

I am setting up a website that will have users. Users can search for songs and (if signed in) save them to a playlist or their library of songs. I want it to be like iTunes in that all the songs on ...
5
votes
2answers
5k views

MySQL Database Schema for Train Timetables

I am interested in what a MySQL database schema would look like for a train timetable. Generally this is presented in table format as the final result. Train No. 11111 22222 11111 Train Day ...
5
votes
1answer
156 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
5
votes
1answer
189 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? ...
5
votes
1answer
348 views

Modelling Hierarchical attributes

I am trying to figure out the data model for a retailer. The retailer has several stores across the country and they are modeled using the following hierarchy: Channel -> Zone -> City -> ...