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

learn more… | top users | synonyms (4)

10
votes
3answers
7k 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 ...
8
votes
2answers
3k 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 ...
3
votes
1answer
940 views

Groups, Users, and Inbox

We're working on a PM system where a user can send an email to other users or groups. We currently have the following tables: Users Groups Users can be part of many groups. A user doesn't have to ...
5
votes
2answers
2k views

Removing Archive Logs after PostgreSQL PITR online-recovery

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1) I followed the official ...
5
votes
1answer
63 views

How to link Similar-but-distinct Models of Medical Imaging Data?

I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two similar,...
17
votes
2answers
18k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
5
votes
1answer
442 views

Temporal database design, with a twist (live vs draft rows)

Not sure if/how I should be cross-posting this... But in case some follow DBA but not SO... Is there a generally accepted means of managing live vs non-live rows in a row-version controlled ...
4
votes
1answer
328 views

What scope(s) is “hasMany” a part of?

I realize, when talking about structures, obviously if you say "post hasMany comments" there are many records about comments relating to each post. What I don't understand is what context these sort ...
22
votes
5answers
18k views

In SQL, is it composite or compound keys?

About SQL (Computing/Databases): When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys? I've ...
8
votes
2answers
399 views

Am I violating any NF rule on my database design?

I am a newbie on creating database... I need to create it for my recruitment web application. My application needs to schedule screenings, exams and interviews of applicants and save result in the ...
5
votes
3answers
1k views

Suggestion required for Large Database Optimization and performance

We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
9
votes
3answers
2k 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 ...
2
votes
1answer
579 views

Which NoSQL database(s) should be used for advertising platform? [closed]

What NoSQL database or combination of databases best meets these criteria? Very fast writes and reads Persistent data that won't get lost in a crash Plays nice with PHP Works well in the cloud (EC2) ...
5
votes
2answers
497 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 ...
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
426 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
279 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
319 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
542 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?
46
votes
12answers
5k views

How could DBAs be more 'programmer friendly'?

The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
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, ...
57
votes
10answers
7k 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 ...
73
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
888 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
619 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
549 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
250 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
57k 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
7answers
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
37k 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 ...
21
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
726 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 now,...