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 (3)

5
votes
2answers
1k views

Vehicle spare parts inventory datadase model

I am modeling a database to be used for vehicle spare parts inventory management and I am stuck... Some spare parts can be broken down from a kit into component parts. I would like to create a model ...
5
votes
1answer
403 views

Design for file based with many events

I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into ...
5
votes
1answer
814 views

How to handle “reference tables” in CouchDB?

What's the best way to handle "reference tables", tables that contain static data, for example a list with various kinds of ingredients for food? Multiple documents, one for each ingredient One ...
5
votes
1answer
845 views

In MongoDB, is having lots of small collections better than having one huge collection?

Huge collections have huge indexes that may not fit into RAM. Is that problem entirely solved with sharding ? If I have lots of small collections with small indexes, what are the options for storing ...
5
votes
1answer
804 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 ...
4
votes
8answers
2k 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 ...
4
votes
4answers
10k views

What's better/faster? MySql or FileSystem?

Let's imagine a web site that is a directory of people. For each person there may be a profile photo and a biography. I'll admit my SQL queries could be better but in general what would be faster and ...
4
votes
2answers
11k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
4
votes
6answers
4k 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 ...
4
votes
3answers
722 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 ...
4
votes
7answers
33k views

How to export an ER diagram from an Oracle DB?

I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...
4
votes
3answers
377 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, ...
4
votes
2answers
6k views

Create table group sub-folders in Management Studio

When I was exploring the master database in SSMS I noticed that under the Tables folder there is another folder called Systems Tables that houses a bunch of tables. Is it possible for us to create a ...
4
votes
4answers
384 views

DB Optimization with 2 to the power of N - Approach clarification

This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know ...
4
votes
4answers
2k 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 ...
4
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 ...
4
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 - ...
4
votes
1answer
6k views

Product Attribute List Design Pattern

I am working on updating our website's product database. It’s built in MySQL but this is more of a general database design pattern question. I’m planning on switching to a Supertype/Subtype pattern. ...
4
votes
2answers
1k 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 ...
4
votes
4answers
885 views

Speed problem with a simple select statement

I'm having a problem with a select statement taking way too long to run. I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 ...
4
votes
2answers
1k views

Data validity and accuracy according to database integrity

What does it mean that data is valid and accurate according to database integrity?
4
votes
1answer
11k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
4
votes
3answers
243 views

Storing Phone Numbers - Proper design

First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career. One of the things I remember (maybe incorrectly) is that when ...
4
votes
2answers
4k 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 ...
4
votes
2answers
419 views

Best way to design this mileage table?

I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source. It's currently set up as so: id (primary_key) , truck_num , start_date , end_date , ...
4
votes
2answers
195 views

Database instance and database

I did some research and finally came to this conclusion: Database instance = processes + memory Database = (physical) log files + control files + data files. Is this understanding correct? ...
4
votes
2answers
544 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 ...
4
votes
2answers
3k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
4
votes
1answer
4k 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 ...
4
votes
2answers
160 views

Conceptual ERD Multi-table many to many, or possibly recursive?

I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
4
votes
2answers
101 views

StackExchange clone: where should I add my indexes?

I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal? Here is the schema in Rails format (SQL format below as well): ...
4
votes
1answer
2k views

Proper tuning for 30GB InnoDB table on server with 48GB RAM

I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
4
votes
2answers
2k views

Do I need a separate Id column for this “mapping” table?

I have a table of Producers and a table of Products, both of which are of the form: Id - int, Primary key Name - nvarchar A Producer can carry multiple Products, so I was going to create a table ...
4
votes
4answers
2k 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 ...
4
votes
3answers
241 views

Is matriculation number a good primary key?

I am trying to design a grade assignment database and i am facing the following dilemma. I have a student table which has the following characteristics: matriculation number which i know for a fact ...
4
votes
1answer
543 views

Deciding between an IsDeleted/DateDeleted column or a separate History table?

We have a table that stores nothing but an AccountId, TagId, and DateCreated. Users can update the Tags on accounts, which adds or remove records from this table. No tag can exist more than once on an ...
4
votes
1answer
102 views

How do you manage a list for particular object. for eg. user comments on specific object(user post etc.)

I am new to database and web application development. I want to know how to store a list for a particular object in a database. For example, user comments for a particular post(object), or the ...
4
votes
1answer
201 views

How can i prevent decryption in SQL Server procedures

I came across in internet tool to decrypt procedures, I checked my database with this tool to decrypt my procedures and it works fine, now the problem is I wish to prevent this kind of tools from ...
4
votes
2answers
735 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 ...
4
votes
2answers
2k views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is ...
4
votes
2answers
507 views

Maintaining referential integrity in a booking system

I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005. I have 3 tables (simplified for this question). Table 1- CourseSize table- this determines the ...
4
votes
2answers
1k views

Is my EER Diagram correct? Why use relationships?

This is the first time I've made an Entity Relationship Diagram (ERD) for MySQL. Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and ...
4
votes
1answer
4k views

Rows with a variable number of columns?

I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter. I'm using code to fragment a tweet into words, usernames, and hashtags, so I'm meeting ...
4
votes
2answers
1k views

Recommended model for application level event logging

I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption. Below is an oversimplified model of the tables and their ...
4
votes
3answers
629 views

What are the pros and cons of using my customer codes as a primary key?

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
4
votes
2answers
2k views

Database design for dynamic Meta fields of an entity that shall be queried intensively

I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, ...
4
votes
2answers
2k views

Creating partitions on a production database

I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals. I have two tables that involve a ...
4
votes
3answers
3k 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 ...
4
votes
1answer
868 views

How to relate two rows in the same table

I have a table where the rows can be related to each other, and logically, the relationship goes both ways (basically, is directionless) between the two rows. (And if you're wondering, yes, this ...
4
votes
1answer
114 views

One database or multiple referencing one?

Database design: one database or multiple databases, which is best? We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications ...