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.

learn more… | top users | synonyms (2)

1
vote
1answer
777 views

Replacing a table with new version

I am a programmer changing the way a process works, and will be using a different table to do this. The process is just sending emails from a web application. Currently the database scripts run during ...
1
vote
1answer
125 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
21
votes
4answers
2k views

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
3
votes
2answers
461 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
25
votes
5answers
6k views

What database technologies do big search engines use?

Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data? What sort of database or technologies do they employ for this? It takes few milliseconds, ...
4
votes
1answer
346 views

Are InnoDB tables harder to backup and slower?

I am trying to optimize our database tables. I have some sql skills, but complete newbie at db administration. Our db operations are pretty slow, and I have been assigned to review the storage engines ...
8
votes
4answers
2k 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 ...
4
votes
2answers
435 views

Using surrogate key after merging the person's data?

This question is about business intelligence with sql server 2008 R2. If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source ...
13
votes
3answers
2k views

Database Design: New Table versus New Columns

(This was suggested to be be repost here from StackOverflow) Currently have a table .. and need to start adding new data columns to it. Not every record (even going forward with new data after adding ...
3
votes
1answer
755 views

Need advice for a 'tag' table design

My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design: 2 tables option : 1 table for the ...
2
votes
4answers
252 views

How to store a date without the year component

I have to store climate (temperature) data for a few cities. This would mean that each city will have 365 values (ex: The value for Jan 1, is the average temperature for that city for all Jan 1 for ...
1
vote
1answer
168 views

Splitting a table based on business logic

This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach. Typically, I break database design into two categories, Business Logic and Recorded Data. ...
1
vote
2answers
328 views

How do I design a database to store quotes online?

I have a form to generate quotes online. The form has many products with their respective prices and options. The users will generate the quote online and I need to store the quotes of the users ...
10
votes
3answers
947 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
1answer
256 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 ...
3
votes
2answers
801 views

php/mysql database redesign and migration: changing databases to tables

I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old ...
2
votes
1answer
1k views

Default values in SQLite3

When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify ...
3
votes
0answers
171 views

Circular relationship [closed]

I'm trying to design a database model and I have a question. I need to represent this: An order could have zero or one QAP. An order could have one or more E-Report. An E-Report must have one QAP. ...
0
votes
1answer
67 views

Unknown relationship

I have a problem with a database design: I have an EReport. This EReport will have a QAPCategory. User must select a QAPCategory from an set of QAPCategory. To know which QAPCategory can be ...
1
vote
2answers
262 views

Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want ...
1
vote
1answer
103 views

Same field but different contents

I'm building a database for a divination system. Just to make it easy, I'll explain it in terms of the i ching. Every element of a hexagram is a different table. There's one for yin and yang, one for ...
1
vote
2answers
2k views

is it possible to create a form in the intranet that can be stored in a database?

I am trying to create a form for my companies intraNet site. The idea is as follows: An employee wants to make a purchase, so they will go to the appropriate page in the intraNet They will fill out ...
4
votes
2answers
555 views

Good reason to denormalize?

I have a database with about 40-50 tables. All but 5 are part of a giant hierarchy of 1:M relationships that all point back to one solitary parent (call it "Project"). Each table is joined to its ...
0
votes
1answer
505 views

Model for Distributed Database [closed]

I want to design a model for a distributed database solution. Are there appropriate tools and approaches? How to include stereotypes for (horizontal) fragments?
6
votes
1answer
187 views

Use multiple columns for location or supertype/subtype?

Given an Equipment entity, it has a location, generically called Whereabouts. This could be a reference to: a User entity (the employee who will use and be responsible for the eqipment for the next ...
13
votes
5answers
2k views

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like: CREATE TABLE Book ( BookID INT NOT NULL , BookTitle ...
3
votes
2answers
228 views

Both Primary and Foreign Key Needed on Table?

Someone recently asked this question: There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called ...
24
votes
3answers
1k views

Is using multiple foreign keys separated by commas wrong, and if so, why?

There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called DealCategories with the following structure: DealCategoryId ...
2
votes
4answers
1k views

Table with contacts: how to differ between people and organizations?

In several projects, I've seen the same problem pop up. But so far, I have not found a satisfying solution. Here's the issue. I need a table with contacts. These 'contacts' could be individual people, ...
2
votes
1answer
575 views

How to translate high level entity relationship into schema

According to the following articles written by Dr. Chan, we can describe high level entity relationship. (see page-10,11 of doc-A and page9 of doc-B) •doc-A) English Sentence Structure and ...
3
votes
2answers
629 views

Recommendations for building a big search engine

I am creating a image search engine that would probably have millions of images tagged with keywords. Basically it would be something like Google Image search. So I want to ask for any tips on ...
5
votes
3answers
814 views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
6
votes
6answers
674 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: ...
0
votes
2answers
501 views

Database Design Advice for Expert Systems - Rule Based Systems

Expert Systems are a branch of Artificial Intelligence. These systems try to capture knowledge of Experts via set of rules. Most of the rules are stated in natural language. But they need to be ...
2
votes
2answers
168 views

A Huge Database: Picking the right schema and data model

We want to store some genomic variant data but there are some problems, more important ones like problem of the data's immense size and variability. 1) Variant data can be huge. For example, a single ...
1
vote
1answer
1k views

how to set/change the font style in database?

This question may be duplicate with another but my question is this. I want to set or change the font style in header of table (like hindi font or any other) of database. I am using sql server 2008 ...
1
vote
1answer
69 views

Database design problem: where to put UNIQUE constraint

I have the following three tables: Form ----------- formId INTEGER PK name NVARCHAR(50) NOT NULL Block ----------- blockId INTEGER PK name NVARCHAR(30) NOT NULL ...
1
vote
2answers
824 views

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
2
votes
1answer
653 views

How to design database for tree view with an infinite depth

I am trying to combine C# and SQL2005 for an application which need to have tree view control. One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That ...
2
votes
2answers
2k views

Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data. Many of the web service calls ...
0
votes
1answer
240 views

Write Access Client App for SQL Server 2005 Backend [closed]

I'm writing an inventory/payroll system, and I'm storing all the records on a remote server running SQL Server 2005 on Windows Server 2008 R2. I was wondering how I could start writing forms(very ...
0
votes
2answers
676 views

schema analyzer tool

I am currently using TOAD to connect to the database. I have around 20 tables that I have been asked to analyze ( I am new to project ). I think the first place to start is to understand the relation ...
3
votes
2answers
4k views

What are the limitations of Toad for SQL Server freeware? [closed]

I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial ...
15
votes
4answers
1k views

Database design: how to handle the “archive” problem?

I'm pretty sure a lot of applications, critical applications, banks and so on do this on a daily basis. The idea behind all that is: all the rows must have a history all links must stay coherent it ...
0
votes
1answer
647 views

How do you query supertypes to get subtype data?

I'm a bit of a DBA newbie here (coming from a programming background having to fill the role of a DBA), so please excuse by blantant and somewhat basic ignorance. Using a simple relationship between ...
1
vote
1answer
1k views

Weak entity and one-one or many-many relationship

In Entity Relationship Model, can the supporting (or identifying) relationships of the weak entities be many-many or one-one rather than many-one? Suppose I have a customer entity (C), a video entity ...
4
votes
3answers
1k views

What is a common way to save 'debit' and 'credit' information?

I'm working on an accounting system, and for each transaction I need to save if this is either debit or credit. I can think of two ways (MySQL database): METHOD 1 Amount (decimal) Type (enum, ...
4
votes
1answer
630 views

barcode inventory design - transaction or master?

I am building a inventory software for mobile phone retailers. There are several things confuse me. Ok, we have: Product Product Attributes Barcode Stock In Batch (Purchases/Return, etc) Stock Out ...
0
votes
1answer
399 views

mysql picasa like photo tags store and query [closed]

I want to make a photo website, allow users to upload photos and write 1-10 customs photo tags, like picasa. I use mysql. Tags could be words or short sentence, but total length can not over 80 ...
5
votes
3answers
3k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?