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)

4
votes
3answers
710 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
3answers
374 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
5k 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
7answers
32k 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
4answers
377 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
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
871 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
3answers
235 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
413 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
189 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
1answer
10k 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
2answers
530 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
2k 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
2answers
132 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
100 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
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
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
3answers
238 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
526 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
195 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
709 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
500 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
604 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
770 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 ...
4
votes
1answer
221 views

250 billion rows - computing interconnectedness on a really huge scale

I have a table of approximately 700k rows, each identified by a unique itemnumber. Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
4
votes
2answers
3k views

How to store formula method in the table

I am trying to figure out how to store formula method in the table (I could create category_formula table). Each category will have different way of formula method to calculate the point and I need ...
4
votes
1answer
149 views

SQL Server 2005, Foreign key check against part of a table

I have an articles table that references a categories table. I'm defining the foreign key like this: constraint fk_categoryid foreign key (categoryid) references categories (categoryid) on update no ...
4
votes
1answer
369 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 ...
4
votes
3answers
549 views

Should I use MongoDB or a NoSQL solution at all for this?

I apologize if this belongs in web applications or something. I'm creating a web application that is sort of a similar concept to github. Basically I plan to allow someone to create a design of ...
4
votes
3answers
698 views

Table structure for many custom profiles

I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers. For example, I have a basic profile that is ...
4
votes
2answers
212 views

Terminology for table types

I am currently unsure of the correct terminology for types of tables that exist, or indeed whether there is a term for the examples I have. I have included my current understanding of the types that ...
4
votes
2answers
3k views

Auto-increment key and Foreign Key as composite for relational database

I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the ...
4
votes
3answers
2k views

Primary key should be chosen so as attributes never change?

I was reading about keys in this DatabaseSystems book - The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person ...
4
votes
2answers
588 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 ...
4
votes
2answers
2k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...