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)

2
votes
1answer
94 views

Help with database design

I'm quite the beginner when it comes to databases, so please bear that in mind. I need to store user statistics in several categories for an application. In the near future I will most likely need ...
4
votes
1answer
147 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 ...
3
votes
1answer
364 views

How to design a database for financial bond prices?

I have following flat file table composed of bond deal names and asociated prices. Each deal name is identified by its unique CUSIP and ISIN codes and each deal name is classified with a certain ...
5
votes
2answers
953 views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
9
votes
2answers
1k views

Design Pattern - One of Many Parent Tables

I come across a situation in database quite frequently where a given table can FK to one of a number of different parent tables. I've seen two solutions for the problem, but neither is personally ...
1
vote
0answers
119 views

key value database

I have one object and a lot of data associated with that object, you can think of it as something like facebook timeline where we have user and user has a lot of posts. I cannot store data in JSON ...
2
votes
0answers
2k views

Schema for an RBAC (Role-based Access Control System)

I want to implement an RBAC, Flat based. I've decided to base my system on the NIST standardization model. Although all fine and well, I have an inclusion to that design. Instead of a hierarchy ...
6
votes
4answers
7k views

Is there a tool to check if my database is normalized to the third normal form?

I learned about normalization recently, and understand how important it is when implementing a new schema. How can I check if my database is 2NF or 3NF compliant ? Manual review is a sure option, ...
1
vote
1answer
2k views

Planning simple CMS: How much relation?

I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
1
vote
1answer
276 views

One address table for many types of addresses

I have an application where I have to store adresses of multiple types of location. For example, my app requires addresses of people, hotels and event venues. Should I create separate tables for ...
5
votes
2answers
1k views

Partitioning a table vs moving entries to another table

I don't understand how partitioning helps me more or less than using a separate table to store older records. I need to maintain a list of stock entries along with historical records about the stock. ...
3
votes
1answer
190 views

Economic Database Architecture Possibilities w/ Data of Varying Length

I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, ...
4
votes
1answer
5k 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. ...
2
votes
2answers
3k views

How would you design a table for a booking system?

I have a design database for a booking system. The booking system requirement are as follows: The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday. ...
1
vote
3answers
396 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
1
vote
2answers
253 views

What are the pros and cons of this schema for flashcards app?

I am planning a flashcards app with the following schema: user(id, name, email) flashcard_group(id, user_id, group_name, group_tag) flashcard(id, group_id, question, solution) guess(id, user_id, ...
1
vote
1answer
127 views

Is MySQL replication suitable for a Write Mostly application?

I'm considering adding another server to my one-server setup application with 600GB MySQL DB. The db and the apache are both running from the same server. In order to avoid backup downtime and improve ...
4
votes
1answer
575 views

MySQL database normalization 3NF

I have homework where I need to create simple web interface and a DB normalized to 3NF. I've chosen ISP client selfcare as my database purpose. Web interface is no problem, the normalized DB is. After ...
2
votes
1answer
126 views

Unique foreign key constraint between three tables

I'd like to track appointment attendances. Visitors can have appointments, or they can just turn up. If they have an appointment, and miss it, in which case I'd like to record a reason for the missed ...
4
votes
1answer
422 views

Should I Link a Fact to Hierarchical Dimension at ALL Levels or only the most granular?

Related question about the same project here. I'm looking at two approaches to model a hierarchical relationship between my fact and dimension tables in a data warehouse for storing IT infrastructure ...
2
votes
1answer
217 views

Representation of circular references constraint

We have a database where we store commands and properties for devices that may be controlled via a network. For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains ...
0
votes
1answer
206 views

Mobile Phone & Tariffs

I have designed database tables of Mobile Phones & Tariffs. It should support multiple mobile networks, sale type and the related affiliate. Points (for operators) are vary depending on the ...
2
votes
2answers
512 views

How do I optimize this database design?

I need to create a vehicle tracking database. There are several vehicles that send data, consisting of around 20 parameters, to my database. The parameters of a vehicle include the constants ...
0
votes
1answer
137 views

Database column vs row size

I have Column A and Column B in a single table. I need to add 1,000,000 records to this table. I have 2 options: Keep 2 columns and add 1,000,000 records Add more columns and flatten out the ...
6
votes
4answers
628 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
3
votes
1answer
449 views

Curious Database building Problem (ER Diagram)

I am trying to build an EER Model for a Autostore that has 5 locations and offers a range of auto products. They offer car repairs and roadworthy tests as a service also. I need to be able to make ...
3
votes
1answer
82 views

linking databases for reporting

I am attempting to design a simple reporting feature for some financial market data. I have a few sets of data, and I am trying to figure out what will be the best way to organize the tables, in ...
1
vote
3answers
2k views

ER Modeling; multiplicity basics - one to many or many to many?

Apologies for a basic question, but I’m having difficulties determining the multiplicity constraints for a database I’m trying to design. Our team undertakes a number of services/projects...each ...
3
votes
3answers
374 views

An “unlimited” data model

I need to store an unknown amount of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All ...
2
votes
2answers
480 views

Replacing composite key with surrogate

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current ...
5
votes
2answers
178 views

Web Database - How to store user information

I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a ...
3
votes
1answer
412 views

Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

I am not sure if this question has been asked or not. At least I couldn't find it. I am curious about a primary key in terms of efficiency with data searching and retrieval. This is a hypothetical ...
1
vote
1answer
2k views

MYSQL Social Networking website - Database Design, Data Growth and Hardware

Introduction I have a system around one million users (community based). It has around 6000 groups (fixed , no change). But there is no discussion board for these groups yet. Building discussion ...
1
vote
5answers
780 views

SQL Server - Database per company. How to query across databases?

We are designing a product that, as per the client's request, must utilize a database-per-company design approach with one shared database holding common data. We are using SQL Server 2012 for the ...
2
votes
1answer
2k views

Why is a Star Schema more normalized than a 3NF Schema?

According to Oracle's documentation, third normal form schemas "may require less data-transformation than more normalized schemas such as star schemas". I'm confused, I thought 3NF is the most ...
1
vote
2answers
228 views

Can graph databases be easily distributed?

I have never worked with graph databases such as neo4j, I would like to know if they support easily replication and clustered distribution as well as key-values databases, or if there inherent ...
4
votes
1answer
1k views

Modelling Relationships in an E-R Diagram, Table or Foreign Key?

Should a relationship in an E-R diagram be created in the database as a table or a foreign key? For example: a tutor lectures a class I see it as three possibilities: there is a "lectures" ...
1
vote
4answers
506 views

How do I develop this data model without a circular reference?

I am trying to develop a concept for a new call tracking system for a help desk. The top 3 levels reference each other. I am trying to determine a better way to link these tables, and am drawing a ...
4
votes
1answer
97 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
1k views

Database Design for Questions and Answers

I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to ...
1
vote
1answer
2k views

How to design a table for storing news items?

I have a script that can get result from news websites and it will run every hour. The php script will get the title and content. The things I want to store in MySQL are: title content ...
9
votes
3answers
523 views

What should I use? A string or 15 integer fields?

I am developing a student tracking program where I need to store 15 exam marks. I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
2
votes
1answer
237 views

From a design perspective, what is the difference between an inventory system and an asset tracking system?

From what I can see, the database design is similar. One is the consumables that are resident in a warehouse (inventory). The others are items, capital, that are assigned to people or places, but ...
12
votes
2answers
819 views

Modelling constraints on subset aggregates?

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
5
votes
1answer
1k views

EAV structure explained in Layman's terms

I've read several articles online that explain how EAV structures work, but there are different interpretations of them. Some articles mention that it's a representation of a data model metadata (or ...
-2
votes
1answer
265 views

Compare MongoDB and MySQL

Can anyone help me to make right decision for choosing scalable and reliable database? I have to create 3-4 tables with around 200 columns each and these tables will have around 5000 data records. As ...
3
votes
3answers
309 views

Database design for changing number of fields

Google Contacts lets you to enter more than one phone number, or email address for a contact. You can add as many as you want. I don't know if there is a limit, other than practical list size. To ...
4
votes
2answers
477 views

Many to Many Relationship Question

I have a database, with one main table: Project, and about 10 other tables (Entity1, Entity2, etc.) Each of the different entities can belong to one or many projects. I know I can have tables like ...
1
vote
0answers
246 views

Improving database design for category based site

I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
0
votes
1answer
227 views

How to simplify schema for multi database / hundreds of millions of rows data set

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started ...