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)

4
votes
1answer
3k 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
861 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
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
1k 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
2k 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
3answers
204 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
383 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
107 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
372 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
198 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
2k 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
146 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
2answers
3k views

database schema design for school system

I am trying to create a database to store information about a school. I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes ...
4
votes
1answer
348 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
2answers
186 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
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
3answers
508 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
3answers
525 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
2answers
2k 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
1k 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
558 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
1answer
419 views

Would appreciate some suggestions on how I should model this data

I am setting up a website that will have users. Users can search for songs and (if signed in) save them to a playlist or their library of songs. I want it to be like iTunes in that all the songs on ...
4
votes
2answers
773 views

Constraint many to many table between two child tables

New to this exchange site so I hope my question isn't inappropriate. Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently. One table has a ...
4
votes
1answer
208 views

Is there some drawback to define SPARSE columns?

I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?
4
votes
1answer
2k views

Single database schema or multiple schemas?

I'm designing a website for an annual event. The events are similar but mostly independent year-to-year. I see 2 possible designs: A single schema with most tables having an EventID column to tag ...
4
votes
1answer
318 views

Proper Database Partitioning

I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
4
votes
2answers
3k views

MySQL Database Schema for Train Timetables

I am interested in what a MySQL database schema would look like for a train timetable. Generally this is presented in table format as the final result. Train No. 11111 22222 11111 Train Day ...
4
votes
1answer
99 views

Should I always make an attribute id that's an primary key?

I was thinking, sometimes several columns in a table uniquely specify the whole row. In that case, is there a need to create a special column for a primary key? Is that good for something?
4
votes
1answer
178 views

Symmetric self-referential many-to-many

I'm trying to model a hierarchy of categories where a category can have multiple parents ( an overlapping tree model as described in this book ) I have the following tables video_categories int ...
4
votes
1answer
233 views

How should I model a worflow of status/activities ? (rules like “if is 3 go to 4, if is 4 go to 5” etc)

I'm trying to model a workflow (status and activities) to create "rules". Let me give you an example. The main "object" let's say are "vehicle requirements", I will have this table: idRequirement, ...
4
votes
2answers
493 views

How should I best design the tables and relationships, given the following rules?

In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
4
votes
4answers
184 views

Database design: where do I put this data?

EventCategory ============= EventCategoryId PK, Name Event ===== EventId PK, EventCategoryId FK, Name This is what I've got now. However, events of some event category type need extra data fields. ...
4
votes
1answer
391 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 ...
4
votes
2answers
470 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 ...
4
votes
2answers
551 views

Designing an aggregation schema

The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it ...
4
votes
1answer
126 views

unique constraint on a set

With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005? To illustrate the problem, I make an example: A tag_bundle consists ...
4
votes
1answer
386 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
4
votes
1answer
427 views

Table structure for keeping track of multiple similar accounts

I'm creating a database to keep track financial information for about 30 different accounts. So far this has all been handled with each account as its own Excel table with the different years across ...
4
votes
1answer
565 views

How to use a relational database to store all users who belong to a group, and all groups which a user belongs to?

This is a beginner question: A user can belong to many groups, and a group can contain many users. Let's say the two tables look something like this: user_id user_name user_email group_id ...
4
votes
2answers
447 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 ...
4
votes
1answer
2k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
4
votes
3answers
561 views

How do I model data from arbitrary CSV's in a database?

I am setting up a web interface for data management. Users upload CSV or similarly structured files, and I want to store these in the database so they can do manipulations on them -- filtering, ...
4
votes
1answer
57 views

Want to add a “variable” to a table

I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
4
votes
3answers
756 views

Single versus Multiple databases

I am working on a hospital management system using DB2 with pureXML. Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use ...
4
votes
3answers
531 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
121 views

How to store immutable, unique, ordered, lists?

I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists ...
4
votes
2answers
183 views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
4
votes
1answer
227 views

Modelling Hierarchical attributes

I am trying to figure out the data model for a retailer. The retailer has several stores across the country and they are modeled using the following hierarchy: Channel -> Zone -> City -> ...
4
votes
2answers
399 views

What happens to the index of a primary key after a DROP CONSTRAINT?

I am running PostgreSQL 9.1.4. I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to : 1 - Remove the pkey constraint on the ...
4
votes
1answer
118 views

Set the primary key for many to many relationship

For the two relations, Customer and Equipment: There is a limited number of equipments (stock) and upon hiring I need to store the date of hire and the expected return date. I sketched this: ...