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)

10
votes
2answers
389 views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
0
votes
2answers
79 views

Advice on choosing a primary key

I will have to do this process in EXCEL first before it is made into a database. I will have many files, but there is about 7 - 9 files that work all together as one e.g. X192AD1-test.docx ...
2
votes
2answers
82 views

Is it safe to use “Safety” as a schema name?

I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does ...
3
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 ...
2
votes
1answer
134 views

How to make a table with fields that are other tables in MySQL?

I am making a music library system with php in MySQL. In this system there would be a table named Songs. Since many songs can be by the same Artist (Person), I wanted to store a table of Person that ...
2
votes
1answer
124 views

Combined or seperated tables: File and Message?

Is this the best approach to design this table in the purpose of sharing the file online between my customer and I? Should I seperate Message table and File table or combined them together? Combined ...
7
votes
5answers
989 views

Best way to document database mappings for migrations

I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this? Excel is a very flexible way to document simple ...
3
votes
2answers
1k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
2
votes
1answer
186 views

Is dimensional modelling useful for modelling tables of calculations?

I have a set of tables that look a bit like this: Key 1 | Key 2 | Key 3 | Var 1 | Var 2 | Var 3 | ... | Var 200 ------------------------------------------------------------- X | Y | Z | ...
4
votes
1answer
383 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 ...
2
votes
2answers
1k views

What is the right time to use an ENUM versus a normalized types table in MySQL? [duplicate]

Possible Duplicate: Advantages and Disadvantages to using ENUM vs Integer types? Considering MySQL only... I'm curious to know what the community thinks the proper use-case for an ENUM ...
2
votes
3answers
672 views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
1answer
260 views

determining user types upon authentication

I've got 3 different types of users (entities) that will log into a website in developing for a client. "FITTER", "CLIENT", "ADMIN" the first method I came up with was this. CLIENT TABLE | ...
4
votes
4answers
303 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 ...
1
vote
3answers
123 views

If A is next to B, how to automatically assert that B is next to A

I am working in Access 2010. Let's say I have a database that tracks where packages sit in a warehouse. Table: Packages PackageID (PK) PackageHeight PackageWidth PackageWeight PackageOwner (etc) ...
1
vote
1answer
258 views

Conditional one to many queries in mysql

I have a simple patients table and lots of Lab tests table. Every patient can have many Lab tests. What i want to do is to only query the tests which are already entered for that specific patient and ...
0
votes
3answers
441 views

SQL Server : primary keys advice to my whitepaper needed

I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should ...
1
vote
2answers
517 views

database normalization 2nf, 3nf

I am doing homework about database normalization. I have to decompose the table into 3nf form showing the steps here is the initial table: ...
2
votes
1answer
542 views

Separate Schemas by Industry or by Application functionality?

I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web ...
2
votes
1answer
3k 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 ...
10
votes
2answers
917 views

Schema-less/flexible + ACID Database?

I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be ...
1
vote
1answer
93 views

keeping database columns consistent with documentation

For a project, the client wanted to be able to add fields to any item and have them be searchable. To make them searchable, that would require the fields to take a column, but that would make the ...
1
vote
1answer
98 views

how to handle so much multiple value strings [duplicate]

Possible Duplicate: Multiple values in lookup fields i have a table has about 27 fields are just strings and every one of them have multiple values for example itemType Field have more ...
2
votes
1answer
347 views

Multiple values in lookup fields

I know newer versions of MS Access allow multiple selections in lookup fields. For example, if I'm collecting information on my students, I can have a field called Race with lookup values that ...
0
votes
1answer
750 views

Database design : multiple category of users login into same system

In our new system we have three type of people Business owner Assistant of business owner Staff (a skilled employee) I am having following entity [Business] - include detail about business ...
2
votes
2answers
825 views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
2
votes
1answer
85 views

Is it dangerous to have duplicated entries in a table?

My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this: Music_id | Music_title | Music_author | Music_link 1 Look at me now ...
7
votes
2answers
4k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
0
votes
1answer
171 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
8
votes
1answer
177 views

Relational database: in-RAM partitioning? Theoretical structure discussion

I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point ...
3
votes
2answers
166 views

Is there any better approach in designing this database?

CUSTOMER: CustomerID (PK) Name Email PAYMENT: PaymentID (PK) Amount Date PRODUCT: ProductID (PK) Type Name Price PURCHASE: CustomerID (FK) PaymentID (FK) ProductID (FK) Quantity Or ...
1
vote
3answers
264 views

One to one relationship or another approach?

I need some addition thoughts on how to approach a database design I'm working on. The issue is I have three related items... * Clan (has many games) * Game (belongs to a clan, has many teams) * ...
2
votes
3answers
395 views

File name as primary key?

I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key. For instance... "someimage.png" as the ...
2
votes
2answers
992 views

Good book on databases for beginners [duplicate]

Possible Duplicate: Must-read Books for DBA I'm already reading a book on Introduction to SQL (RDMS). But that only teaches SQL and commands and I feel like my knowledge lacks in database ...
2
votes
2answers
2k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
2answers
2k views

database schema for a product with multiple categories and hierarchical categories

I need some opinion on whether I am going in the right direction with this. I am trying to build a e-commerce site (with MySQL backend) where a single product can belong to more one than category ...
6
votes
5answers
4k views

Importance of ER Diagrams

I am a student and I am developing several projects as a part of my academia. While developing the database for one of the projects, we came across a situation where we thought about whether ERD is ...
4
votes
1answer
931 views

How to keep long multiple of questions and answers in database?

I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for ...
5
votes
2answers
923 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 ...
2
votes
3answers
429 views

Physical Address Schema

I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked). Store the address ...
6
votes
2answers
280 views

Definition for “logical difference”?

I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
1
vote
1answer
161 views

Foreign key to subtype or super types?

I'm using SQL Server and in this case i have the following tables: Restock: Foreign key to an R&D Staff member R&D_Staff: subtype from Staff Sales_Staff: subtype from Staff Shipping_Staff: ...
2
votes
2answers
566 views

Generalisation in databases

I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c. Is it better to ...
2
votes
3answers
2k views

Sorting by DATETIME slower than by TIMESTAMP?

I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ? Because I noticed that ordering by numbered keys is done ...
3
votes
1answer
535 views

Do I have duplicate key indexes?

CREATE TABLE relations ( object_id BIGINT(20) NOT NULL PRIMARY KEY, object_term BIGINT(20) NOT NULL, UNIQUE KEY link(object_id, object_term), CONSTRAINT fk_term FOREIGN KEY(object_term) ...
2
votes
2answers
1k views

6th normal form, Recomposition query, efficient implementation

I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the ...
1
vote
2answers
160 views

Designing an Accumulating Snapshot with Repeated Groups, need advise

Situation I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School. At the student level it has to show the trend of the student over ...
1
vote
3answers
169 views

Should I create a separate database table for temporary users

I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These ...
1
vote
1answer
944 views

User system database design

I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement/test. It basically consists of users, events, user profiles and a messaging ...
1
vote
3answers
2k views

how to Export a document contain tables name, columns names and column description from SQL Server 2008?

I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column ...