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)

6
votes
1answer
1k 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 ...
1
vote
1answer
55 views

delimited values in a column value

I have a table which have a * to * relationship with another table. Lets say the first table is T1 and the second table is T2. So I need to keep the T2 Pks linked with single row in T1. I really like ...
0
votes
2answers
3k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. ...
0
votes
2answers
73 views

Best practice for entry position indicators

My problem is, that I have no idea how I could cleanly make an index (not a real Table index, just an indicator) for each table row representing the entries position while each position must be unique ...
5
votes
4answers
14k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
3
votes
2answers
3k views

Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)

Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ?? Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
5
votes
1answer
306 views

Database structure, relational or data warehouse?

Good morning, Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns: Date ...
2
votes
4answers
1k views

One table with many rows or many tables with a few rows

I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at ...
0
votes
3answers
2k views

Will using bigint vs mediumint have a performance impact?

I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I ...
2
votes
1answer
65 views

Creating a schema for various sizes of varchar

I am redesigning a database schema for an application I am working on, and one of the major changes I am adding is the ability to dynamicly add new "fields" into a form based administration area. ...
2
votes
3answers
2k views

Is a two column primary key the most performant way to go if (a,b) should be unique but I will only be querying against a?

I have a table with two columns, A and B. Each pairing (a, b) should only occur once in the table. When I query this table, I will only be interested in those rows where A has a certain value. Is a ...
3
votes
4answers
783 views

Should we add extra 5 columns or build a separate table?

Currently we have a table with 35 fields in it. Now, we got a requirement to add 5 extra columns but these columns will not be fill always. So, I was thinking just add one column and have that id as ...
1
vote
1answer
968 views

Implementing a family database using Access 2010

A while ago I asked this question about using Access 2007 to implement a family tree model. I had all the tables and relationships mapped out in a UML diagram but realised it was getting out of hand ...
3
votes
3answers
935 views

Comparison Tool for MySQL data base schemas [closed]

We'd like to update an old database schema for a new software release that requires changes in database schema (additional colums, column renames, table renames, datatypes, ...). Is there any ...
1
vote
1answer
75 views

Configuration Persistance Design

We have a well designed and normalized DB, but, there is a requisit that could cloud this design. This image shows the design of part of the DB. The main functionality of our application is to create ...
1
vote
3answers
196 views

is id and phone number enough to uniquely identify a row in phone numbers

Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the ...
5
votes
2answers
500 views

How should I model an “either/or” relationship?

Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has ...
1
vote
0answers
318 views

Survey System Database Design [closed]

we are trying to design a survey system and we are kind of stuck at database table design. We use jsp, oracle and jquery. we managed to make a survey generator that clones an html table with different ...
6
votes
4answers
319 views

Database Design for updatable sequential records

I am working on creating a data model for storing data related to production tracking. I work for an engineering firm that models and analyzes data for our clients. There are several steps in the ...
3
votes
1answer
219 views

Best mysql db design to solve timezone issues

I faced problem with timezone on my last two projects. So What is best way to sort out problems with timezone on db design. I mean, What format should i added date time stamp. On of my problem. My ...
1
vote
1answer
761 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
124 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
441 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
5k 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
343 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
418 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
701 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
242 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
164 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
322 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
921 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
247 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
776 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
170 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
258 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
544 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
494 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
183 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
227 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
539 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 ...