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

learn more… | top users | synonyms (4)

1
vote
1answer
1k views

Design of fact table(s) and dimensions tables for data warehouse

Hi i'm newbie in Datawarehousing, how would you model this in a Data Warehouse: i wish design the Data Warehouse which give the answers of statistics relating to a baseball league For players in ...
8
votes
3answers
424 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 ...
5
votes
1answer
278 views

What databases or styles of database can use git (or another dvcs architecture) to manage cross-site replication?

I'm looking for a database software package for a dictionary (like the hunk(s) of dead tree that you might have somewhere at home). I would like the database to scale well to a million key-value ...
1
vote
1answer
119 views

Are there situations where queries are formed geometrically?

The topic came up in this other question. We were discussing sign value on a primary key having any significance. If there are good times to use geometry in building queries the sign value could be ...
8
votes
3answers
2k 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) ...
2
votes
1answer
295 views

MySQL data modeling help

I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach. One or more organizations can sponsor an event or ...
3
votes
1answer
176 views

Mysql constraint problem

I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this: +------------+ +------------+ +------------+ | t1 | | t2 | | t3 | ...
5
votes
2answers
13k 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 ...
10
votes
4answers
6k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
5
votes
2answers
318 views

How do I store data with a query that's approximated?

I'm trying to find a way to store my data with fast access (better than O(n)). My database consists of data (4096 byte strings) that represents some information about some items. The problem is, that ...
0
votes
1answer
1k views

Entity Relationship Model for linked tables

Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^ I am getting warmed up with MySQL and I try to understand the concepts of creating well structured ...
6
votes
4answers
538 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?
1
vote
1answer
355 views

Versioning a dataset in an RDBMS using initials and deltas (xpost from SO)

I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, ...
56
votes
10answers
6k views

What are the arguments against or for putting application logic in the database layer?

NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting ...
72
votes
11answers
26k views

Files - in the database or not?

What is the best place for storing binary files that are related to data in your database? Should you: Store in the database with a blob Store on the filesystem with a link in the database Store in ...
2
votes
2answers
1k views

How do I properly design a many-to-many (charges/payments) accounting system?

This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance. Basically I'm having trouble ...
5
votes
3answers
4k 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 ...
2
votes
2answers
878 views

SQL datatype choice for flagging a read/ unread message?

I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value. for example if a message is ...
2
votes
1answer
285 views

What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?

I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it. the system will have a asp.net UI, web services middle tier and SQL2005 back end. ...
2
votes
2answers
612 views

Determining sizes for Separated Partitions of programs, data and logs in SQL Server

As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server. Question is, what is ...
10
votes
3answers
1k 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 ...
9
votes
1answer
89 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?
2
votes
2answers
533 views

Best index strategies for read-only table?

I have a table in SQL Server which has following characteristics: will contain about 1.2M records. first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each ...
3
votes
3answers
1k views

Priority Inbox Database Design

I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox? If you're unfamiliar with the system, it prioritises messages based on the type of ...
7
votes
4answers
7k views

Database design for a time tracking application

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he ...
8
votes
5answers
5k views

What is the most efficient way to alter a column definition in a table with millions of rows

I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple alter table Table1 ALTER COLUMN Column1 XML NULL but it takes forever. So here are my ...
3
votes
1answer
247 views

In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?

I'm working on a recruiting web site and I was wondering what would be best between: A User table, the employer accounts are linked to a company (therefore when companyID is null then it's a job ...
10
votes
5answers
55k 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 ...
22
votes
8answers
11k 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
2answers
36k 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 ...
19
votes
7answers
10k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
9
votes
4answers
13k views

How to design a relationship database table to store friendship relationship?

I want to design a table to store friendship relationship in my web project It should satisfy at least the following 4 conditions: who send the add-friend request e.g.(if A TO B then this ...
11
votes
6answers
18k views

Is there any good tool for making Database Design and Prototypes? [closed]

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

Is polling the only way for updating app's data from a database?

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
1
vote
2answers
714 views

How wrong is my web app modeling schema?

So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years ...
8
votes
5answers
3k 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?.
18
votes
6answers
3k views

Needing A Database Design Book

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 ...
11
votes
2answers
1k 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 ...
6
votes
2answers
6k 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 ...
1
vote
1answer
220 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
3
votes
3answers
1k views

Migrating from ntext to NVARCHAR(MAX)

I have table with the columns of type ntext.And for some peculiar reasons, client want to migrate the column type to NVARCHAR(MAX). While doing so , what all potential threat should i be careful ...
3
votes
1answer
476 views

MySQL Database Schema for Volleyball

I am developing a database for the recording of Volleyball results. I have a fixture database that tells me who plays who in each round. Teams Table, say IDs 1,2,3,4 Fixtures Table, Round No.1, Team ...
10
votes
3answers
421 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 ...
3
votes
1answer
764 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
5
votes
4answers
3k views

Examples of SQL transaction procedures for sales tracking or a financial database

I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions: salesman creates new product ...
5
votes
3answers
718 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 ...
29
votes
6answers
12k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
16
votes
6answers
15k 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?
3
votes
3answers
2k views

Estimate large database size and speed

First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following : +----------+------------------+------+-----+---------+-------+ | Field | ...
2
votes
2answers
390 views

Will this Database design logic work and what should be used?

A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com What he essentially wants to do is ...