Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.
7
votes
1answer
263 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
6
votes
3answers
540 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
6
votes
4answers
397 views
Purpose of IDs in a User Table Database
In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
6
votes
2answers
253 views
What is the name of this schema pattern
Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details.
Essentially, the pattern consists of one database which is 3 ...
6
votes
4answers
348 views
How can a database be normalized when an optional field causes denormalization?
Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this:
A ...
6
votes
2answers
305 views
Data Compression Through Base36 Encoding
I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type.
I'm thinking about using Base36 encoding to accomplish this. Are ...
6
votes
2answers
1k views
Is one-to-one relationship normalized?
Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
6
votes
3answers
231 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
6
votes
4answers
1k views
Is there a tool to check if my database is normalized to the third normal form?
I learned about normalization recently, and understand how important it is when implementing a new schema.
How can I check if my database is 2NF or 3NF compliant ?
Manual review is a sure option, ...
6
votes
3answers
1k 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
3answers
504 views
Is there a standard implementation of a Users Database?
I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
6
votes
4answers
322 views
Can this business logic be enforced by a conditional database constraint?
I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
6
votes
2answers
623 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 ...
6
votes
2answers
1k views
Fact table foreign keys null?
I am new to data mart design and need to clear a few concepts.
I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables.
Now suppose ...
6
votes
1answer
367 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 ...
6
votes
2answers
317 views
Should a log table get an id field or primary key?
I have a log table that captures the datetime stamp of when certain files were exported to another system.
The exportedLog table currently has three fields:
id (primary key)
messageId ...
6
votes
3answers
663 views
Where does the magic column “name” come from?
I got this by accident:
db=> select name from site;
ERROR: column "name" does not exist
LINE 1: select name from site;
^
db=> select site.name from site;
name
...
6
votes
3answers
114 views
Arbitrarily ordering records in a table
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
6
votes
3answers
532 views
Best way to store units in database
I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the ...
6
votes
6answers
435 views
What is the best database design for this situation?
I'm working on building a business application for my company and struggling to pick the most appropriate database design for a particular situation. Let's say I have the following entities:
...
6
votes
2answers
331 views
Proper technique for storing users event data
I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry ...
6
votes
4answers
169 views
Database Design - different objects with shared tagging
My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the ...
6
votes
1answer
517 views
Are there any performance benefits to using a hash table with no clustered index?
I have this table:
CREATE TABLE [dbo].[relatea] (
[mid] [varchar](16) NOT NULL,
[sid] [varchar](16) NOT NULL
)
It stores hash matches. Is there any benefit to having this as a heap? It has ...
6
votes
5answers
1k views
Are there opensource hierarchical database management systems
I was looking for a hierarchical database management system and the only one i came across was IBM's ims.Are there opensource systems that one can use?.
6
votes
5answers
928 views
Survey database design: associate an answer to a user
I'm doing the conceptual model for a survey database.
The goal is store the answers given by users (it going to be an Android app).
I have three entities: user, question and option.
A question will ...
6
votes
2answers
380 views
Using 100's of schemas in a database SQL2008R2?
We are in SSAS type of environment with 100's of clients.
Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being ...
6
votes
3answers
720 views
How to normalize Time Sheet data?
I am having a terrible time finding an appropriate way to store this data in a database.
The current excel (simple) version looks something like this:
http://i.stack.imgur.com/BEZOw.png
Staff and ...
6
votes
3answers
212 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
6
votes
2answers
337 views
Extracting entity from attribute in entity-relationship diagram
I am developing a database model where I need to capture information about rent contracts for parking places within a parking. I have thus defined an entity called PARKING CONTRACT.
Requirements (r.) ...
6
votes
1answer
591 views
What should every DBA know? [closed]
As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
6
votes
3answers
300 views
Representing N:N relation as a functional dependency in a database design
As a software developer, I already have some experience in designing a more or less normalized database schema, but I haven't received any formal training in it before. This semester, I took a ...
6
votes
2answers
377 views
Design Pattern - One of Many Parent Tables
I come across a situation in database quite frequently where a given table can FK to one of a number of different parent tables. I've seen two solutions for the problem, but neither is personally ...
6
votes
2answers
189 views
Definition for “logical difference”?
I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
6
votes
3answers
347 views
Best way to document database mappings for migrations
I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this?
Excel is a very flexible way to document simple ...
6
votes
1answer
89 views
What is a good way to determine the performance I should expect from a PostgreSQL server?
Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
2answers
683 views
How does using separate schemas affect SQL Server 2008 performance?
I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
6
votes
1answer
182 views
Performance of query with a range condition and order by
We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word text
frequency integer
...
5
votes
5answers
2k views
Is there any good tool for making Database Design and Prototypes?
I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
5
votes
5answers
2k views
Importance of ER Diagrams
I am a student and I am developing several projects as a part of my academia.
While developing the database for one of the projects, we came across a situation where we thought about whether ERD is ...
5
votes
3answers
1k views
One Big Database vs. Several Smaller Ones
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,
...
5
votes
6answers
141 views
How to do a bulk load of a database when application are relying on it?
In a daily routine, I need to drop and do a bulk load of a database.
The problem is that I have a WebApp relying on that database. So I can't drop the database just like this.
What would be a good ...
5
votes
3answers
684 views
Plural vs Singular Table Name
How should I name my Tables when creating a new database?
Singular: Client or Plural: Clients?
5
votes
2answers
150 views
Is there a name for this “restricted one to many” relationship? Can it be enforced in the DB?
In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level.
A standard ...
5
votes
5answers
11k views
Matching single column against multiple values without self-joining table in MySQL
We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:
user_id ...
5
votes
1answer
618 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 ...
5
votes
2answers
2k views
Database behind a Multilingual User Interface
This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another:
Suggestion for database ...
5
votes
3answers
455 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
203 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
1answer
432 views
LDF and MDF file sizes for a dynamically created Database
I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. ...
