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.
10
votes
2answers
2k views
How do I map an IS-A relationship into a database?
Consider the following:
entity User
{
autoincrement uid;
string(20) name;
int privilegeLevel;
}
entity DirectLoginUser
{
inherits User;
string(20) username;
string(16) ...
10
votes
3answers
1k views
Column name naming conventions and best practices
I would like some expert opinion on best practices when it comes to column naming.
The background is that according to Wikipedia, the following syntax,
SELECT ... FROM Employees JOIN Timesheets ...
9
votes
5answers
1k views
Is database normalization dead?
I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing ...
9
votes
4answers
1k views
How to store data in a database without tables?
All I learned in school was SQL which saves data to tables.
Right now I am working on a project where data is stored in XML-files. Additionally every XML contains a reference to visual files (JPEG).
...
9
votes
3answers
3k views
What is an audit table?
What are audit tables?
How are they useful?
I came across them reading this article.
9
votes
4answers
559 views
Where should one put indexes in a time dimension table?
After reading the Questions and Answers from this website about indexes, a question came to my mind.
What if, one is using a time dimension table with the lower level of granularity being the day. ...
9
votes
5answers
4k views
Splitting Tables in MySQL. Good practice?
I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.
The tables look like this:
...
9
votes
2answers
223 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
9
votes
2answers
497 views
How do I implement a database/table as a stack
I have a state-machine which needs to push/pop some file-names for different users. I would traditionally use stacks as the choice of data structure, but this needs to be done using a database since I ...
9
votes
4answers
851 views
Is it a good idea to use Synonyms to avoid creating a duplicate table?
We have 3 copies of the exact same database. All 3 databases have a Users table, and a User will always exist in all 3 databases with the exact same settings. Anytime we want to add or edit a User we ...
9
votes
2answers
1k views
Security for Application Developers doing PL/SQL work in Oracle
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
9
votes
3answers
299 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 ...
9
votes
3answers
1k views
Best way to design tournament database
I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.
I have created a mockup ...
9
votes
2answers
482 views
What are the penalties of using many (thousands) of column families or keyspaces in Cassandra?
I am in the process of evaluating the best design for our Cassandra installation.
There is not so much information out there on the Internet about using the first two levels of access that Cassandra ...
8
votes
5answers
893 views
Do empty columns take up space in a table?
I have table that holds from very basic info. Just a title and a few date fields.
There's one field called comments which is varchar(4000)
Most of the time we leave it blank, but some times will enter ...
8
votes
2answers
465 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 ...
8
votes
2answers
1k views
Handling growing number of Tenants in Multi-tenant Database Architecture
Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way ...
8
votes
3answers
670 views
Suggestion for Large SQL Server Database Design
We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very ...
8
votes
2answers
264 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
8
votes
2answers
483 views
Why shouldn't I have one table for multiple relationships?
Assuming I have multiple relations in my database, for example Store, Employee and Sale, and I want to connect pairs with a simple binary relationship. Personally I would create tables named ...
8
votes
4answers
249 views
Is this a standard way to design a database?
I recently learned about how relationships are defined in the database at work, and was wondering if this is a standard practice.
Let's say we have two processes: Process A, and Process B. Process B ...
8
votes
2answers
592 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
602 views
Why can't I use NEWSEQUENTIALID() as the default value for my column?
I'm trying to create a table in Management Studio and after reading about the new (from SQL 2005 on) NEWSEQUENTIALID() function, thought I'd give it a go.
This is what I'm doing:
But it's not ...
8
votes
4answers
468 views
Are there any good reasons for keeping date and time in separate columns?
I'm trying to understand our software vendor's decision to keep date and time in separate columns. For example, when the row was created or updated. Both time and date are DateTime columns. We are ...
8
votes
2answers
199 views
How to design this database to avoid cyclic dependency?
There are two tables:
User
Address
User contains a reference to Address.
Address contains the columns CreatedBy and ModifiedBy, which is reference to User.
How do I design this database to avoid ...
8
votes
2answers
238 views
Modelling constraints on subset aggregates?
This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
8
votes
2answers
8k views
Database model with users, roles and rights
I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user.
Below is ...
8
votes
4answers
2k views
Whether or not to create separate tables for different product types?
I'm in the process of designing a database and I'm having second thoughts about my initial design decisions...
Product types are as follows... Models, parts, replacement part kits and options.
...
8
votes
1answer
605 views
How to manage 3.1 billion rows of data?
I am currently tasked with implementing a storage schema for a relatively large amount of data. The data will primarily be accessed to determine a current data point value, but I am also required to ...
8
votes
1answer
128 views
Relational database: in-RAM partitioning? Theoretical structure discussion
I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point ...
7
votes
3answers
474 views
What should I use? A string or 15 integer fields?
I am developing a student tracking program where I need to store 15 exam marks.
I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
7
votes
5answers
593 views
Database design: Dividing multiple identical tables, good or bad?
I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them.
I have seats at a theater, the seats are divided into 4 main areas ...
7
votes
4answers
460 views
How to create a database for unknown kinds of data?
I am in the process of designing a database for a new PHP/MySql-based application.
My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
7
votes
3answers
457 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
3answers
479 views
What are negative keys used for?
Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet.
When and why is it useful to have negative (or rather signed) ...
7
votes
4answers
245 views
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind:
Simplicity; there are no ...
7
votes
2answers
546 views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
7
votes
5answers
2k views
SQL Server - separate database for reports?
On our SQL Server, we have a database for each of our web apps. For reports, we use Reporting Services and all report data (including report parameters) come from stored procedures.
The stored ...
7
votes
2answers
230 views
Can I losslessly decompose this table?
I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills.
In essence, I have a table with the following primary key:
child_id integer
...
7
votes
3answers
211 views
Database design advice
I'm designing a database for our sales team to use as a quick job quoting tool. I would like some feedback on a particular aspect of the design.
A quote is basically built up by selecting a list of ...
7
votes
1answer
303 views
Choice of authentication approach for financial app on PostgreSQL
First some background.
The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL. We implement a very large amount of business logic in user defined ...
7
votes
2answers
422 views
Appropriate multi-language schema, or overkill?
UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/
I am building ...
7
votes
3answers
154 views
Choice- or ids-list- field representation
I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to ...
7
votes
1answer
155 views
How can I make my database more concise?
I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain ...
7
votes
1answer
748 views
Generating Invoices and Tracking
Every 2 weeks, the system will generate the invoices for the companies.
Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
7
votes
1answer
280 views
Could Stack Overflow run on SQL Server Web Edition?
I am starting a new web venture that may need to scale to a high number of users.
I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in ...
7
votes
2answers
150 views
Best relational database structure for this data
I'm in the process of creating a database scheme for the following scenario:
There are users
Users have roles (such as "Developer" or "CEO")
Roles have applications (such as "Topdesk")
Applications ...
7
votes
2answers
1k views
SQL Server: query performance (search 2 million rows)
Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much?
...
7
votes
1answer
80 views
Which schools are known for graduation level database research?
Which schools across the world are best known for research being carried out in database field in recent times?
6
votes
3answers
525 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 ...
