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

learn more… | top users | synonyms (4)

6
votes
1answer
188 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
1answer
1k views

Designing a database for a site that stores content from multiple services?

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
6
votes
1answer
71 views

Is it useful to split a table into a frequently used and a archive table?

I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom. Is it useful to split this table into a frequently used table ...
6
votes
1answer
135 views

MySQL database design to sort and filter air traffic data

I am struggling to design the correct database / structure for what I'm trying to do. Let me try and explain the situation. Background We are a group of enthusiasts who monitor air traffic via ADS-...
6
votes
2answers
1k views

Database table design question

Firstly, database novice here. I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able ...
6
votes
1answer
3k views

Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field

This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
6
votes
1answer
719 views

ERWIN create and use database

I'm trying to generate physical database from my logical model using ERWIN 7. All is well in the script that is generated automatically, there is only the problem that the model is created in the ...
6
votes
4answers
503 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 ...
6
votes
1answer
334 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 ...
6
votes
2answers
256 views

Single table with more columns vs multiple tables with fewer columns

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows? For example: A user can post ...
5
votes
8answers
4k views

Should you ever not assign an unique ID to each row in a table? [duplicate]

Possible Duplicate: Should every table have a single-field surrogate/artificial primary key? I can't imagine ever not using a unique ID with every table, which is why I'm asking this question. ...
5
votes
6answers
6k views

Any way around unique index 16 column max

According to the CREATE INDEX documentation: Up to 16 columns can be combined into a single composite index key. We've got a table with ~18 columns that need to form a unique combination. This ...
5
votes
2answers
524 views

Likes or votes for posts

I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would ...
5
votes
3answers
820 views

Is a surrogate key better than a natural key in this case

I copied this code from here: CREATE TABLE records( email TEXT REFERENCES users(email), lat DECIMAL, lon DECIMAL, depth TEXT, upload_date TIMESTAMP, comment TEXT, PRIMARY ...
5
votes
2answers
898 views

Introducing an 'age' category in a patients database

I want to do the following in my DB related to patients: when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients how ...
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
2answers
13k views

Questionnaire database design - which way is better?

I have ONE long html page, several sets of questions divided into small sections (approx. 15 sub-sections in one page), the total of questions are about 100 questions: varies from input, multiple ...
5
votes
2answers
775 views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
5
votes
2answers
395 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
5
votes
2answers
1k views

What are the advantages of a database design with single table for MtM relationships?

The database that backs our software product has a table design like the following: Positions ID Name Parts 1 One 12345 2 Two 12346 3 Three 12347 Collections ID ...
5
votes
3answers
396 views

Database Design Issues

My question consists of three parts: When can I be sure that my database design is perfect? Is returning to the database design to change some issues (i.e, adding new column, deleting a column, ...
5
votes
4answers
417 views

Is it possible to get rid of so-called 'Spurious Tuples' completely?

Is it possible to get rid of so-called 'Spurious Tuples' completely? For example: In this example from a textbook, there is an original table: I don't see anything wrong with the two projections ...
5
votes
3answers
2k 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, debit/...
5
votes
2answers
2k views

Data type for currency 'type' column, e.g. usd, cat, etc.

I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details: No more than 10 different ...
5
votes
1answer
2k views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
5
votes
2answers
367 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
5
votes
4answers
230 views

How can I represent discrete ages in a relational database?

I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any ...
5
votes
3answers
2k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
5
votes
2answers
3k 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 ...
5
votes
1answer
5k views

What is a basic model for making a database with users and groups?

I'm trying to find the best way for a basic security system for a website. I know I want users and groups. I thought I'd have: user_table user_id user_name ... group_type group_id group_name ...
5
votes
2answers
98 views

Which executes first? Trigger or rule?

Using PostgreSQL. Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true. My question is: will the rule prevent the trigger ...
5
votes
1answer
530 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
5
votes
5answers
3k views

Using timestamp plus integer as primary key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
5
votes
2answers
5k views

Does order matter for composite primary key in Oracle or any database?

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order ...
5
votes
1answer
364 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 (...
5
votes
4answers
3k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
5
votes
4answers
4k views

Are there any free online collaboration database schema tools? [closed]

I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
5
votes
3answers
2k views

Scalable table structure for periodically updated stats that get aggregated over time

I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later. For example, every 30 seconds I could receive the number of oranges sold at a store in the ...
5
votes
2answers
155 views

Trigger to create symmetrical rows

I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. I'...
5
votes
2answers
1k views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
5
votes
2answers
289 views

Is there a well-known name for this “poor man's ref. integrity” schema design pattern?

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin ...
5
votes
2answers
748 views

Is it ever a good idea to denormalize for integrity?

I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers. These problems can take a variety of forms - they might be multiple ...
5
votes
2answers
34k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
5
votes
2answers
4k views

What Is The Point of a Primary Key?

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than ...
5
votes
1answer
788 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
5
votes
1answer
11k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
5
votes
2answers
2k views

Database Hardware and Software to handle million requests per second?

I'm curious about the necessary hardware and software requirements to support a strong MMORPG. For example, what kind of requirement (both hardware and software) would I need if I were to build a ...
5
votes
2answers
284 views

Table Relationship - One to Few (Many)

I know the title may seem confusing but I have yet to find a solid answer to this design pattern. Let's say I want to create an Employee table and have multiple addresses for each employee. So this ...
5
votes
2answers
557 views

Relation between first name and last name

I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model) I've read that the relation is of type: 1-1: They are linked ...
5
votes
2answers
145 views

Which to store: miles or kilometers?

My app is launching to a US audience first, but I want to internationalize the database schema for future expansion. Obviously I will convert between the two units as necessary regardless, but are ...