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.
11
votes
6answers
3k views
Storing IP address
I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?
Should I support IPv6 as well? If so, what is the ...
6
votes
3answers
472 views
Is there a standard implementation of a Users Database?
I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
5
votes
2answers
2k 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 ...
13
votes
5answers
4k views
When should you denormalize?
I think we are all familiar with database normalization.
My question is: What are some guidelines that you use when you want to denormalize the tables?
6
votes
2answers
601 views
Database for building a realtime analytics system
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on
Real time counting of unique URI visit/PV by different ...
5
votes
3answers
1k views
SQL Server Primary key / clustered index design decision
Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.
I've got a product history ...
6
votes
2answers
347 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 ...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
5
votes
3answers
2k views
Inventory database structure when inventory items have varying attributes
I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using ...
5
votes
3answers
1k views
One Big Database vs. Several Smaller Ones
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,
...
5
votes
5answers
1k views
What is the most efficient way to alter a column definition in a table with millions of rows
I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple
alter table Table1 ALTER COLUMN Column1 XML NULL
but it takes forever. So here are my ...
3
votes
2answers
264 views
Help with a multi-language medical database
My project is to design a database for medicines in many countries.
Medicines have various criteria like composition, price, manufacturer.
Database will be multi-language; related to each country.
...
3
votes
1answer
427 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 ...
2
votes
3answers
260 views
Simple scenario needs to go from Unnormalized Data to 3NF - Review Please
NOTE: The Question has been updated. View Edit Revisions to view original question.
The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so:
...
11
votes
5answers
492 views
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?
Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:
CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle ...
11
votes
3answers
11k views
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of ...
9
votes
2answers
482 views
What are the penalties of using many (thousands) of column families or keyspaces in Cassandra?
I am in the process of evaluating the best design for our Cassandra installation.
There is not so much information out there on the Internet about using the first two levels of access that Cassandra ...
7
votes
3answers
479 views
What are negative keys used for?
Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet.
When and why is it useful to have negative (or rather signed) ...
6
votes
3answers
114 views
Arbitrarily ordering records in a table
A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
6
votes
2answers
250 views
What is the name of this schema pattern
Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details.
Essentially, the pattern consists of one database which is 3 ...
6
votes
4answers
4k views
How to design a database for storing a sorted list?
I am looking to store a sorted list inside a database. I want to perform the following operations efficiently.
Insert(x) - Insert record x into the table
Delete(x) - Delete record x from the table
...
6
votes
2answers
325 views
Proper technique for storing users event data
I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry ...
5
votes
2answers
180 views
Design: How do you decide when items are related vs when they are attributes?
I've been trying to figure this out for a while. Let's say you have a person, for example. An attribute of a person is his or her social security number, right? But a person also has a social ...
4
votes
1answer
816 views
2NF decomposition + database normalization
If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
3
votes
2answers
2k views
Select column names whose entries are not null
I would like to have a list of those columns of a table that have at least one not-NULL data entries in them.
In other words, I would like to get the column names for which the following returns at ...
3
votes
2answers
1k views
What are the limitations of Toad for SQL Server freeware? [closed]
I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial ...
3
votes
1answer
274 views
Survey database design: first version. Are there errors?
I have to represent surveys with questions like these ones:
Studying those groups of questions I found these rules:
A survey has questions' groups.
A questions' group has a name and questions ...
3
votes
2answers
384 views
What type of datastore should I use for high volume time series data?
Imagine a project which requires to be able to process 50,000+ (tiny ints) writes per second. The data, once inserted, won't change. The data is a measurement on a particular time. Sub second ...
2
votes
4answers
1k views
Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping
I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored ...
9
votes
4answers
559 views
Where should one put indexes in a time dimension table?
After reading the Questions and Answers from this website about indexes, a question came to my mind.
What if, one is using a time dimension table with the lower level of granularity being the day. ...
9
votes
2answers
1k views
Security for Application Developers doing PL/SQL work in Oracle
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
7
votes
5answers
595 views
Database design: Dividing multiple identical tables, good or bad?
I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them.
I have seats at a theater, the seats are divided into 4 main areas ...
6
votes
4answers
393 views
Purpose of IDs in a User Table Database
In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
5
votes
2answers
174 views
Is there a reason/benefit I am not aware of for this kind setup?
I now manage a MySQL database that was designed and created by another.
I am confused about a certain setup that the original developer created..
He did not use auto increments for table IDs. ...
5
votes
1answer
588 views
What's difference between standard view and indexed view in SQL Server [closed]
I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't?
Indexed view needs to be ...
5
votes
3answers
439 views
how do I model this in database?
I am using mysql as my backend database. Here is the use case;
A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
5
votes
2answers
526 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 ...
4
votes
1answer
96 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
4
votes
1answer
277 views
SQLite3 simple database design question
I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database.
The ...
3
votes
2answers
178 views
Both Primary and Foreign Key Needed on Table?
Someone recently asked this question:
There are two tables: Deal and DealCategories. One deal can have many
deal categories.
So the proper way should be to make a table called ...
3
votes
2answers
207 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 ...
3
votes
2answers
218 views
Data Model For Summarizing Student Info
I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame. I could certainly go retrieve the count from the attendance ...
2
votes
2answers
156 views
Please review my first thought-out attempt at a database schema
I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL. I would like, if someone has time, to review my layout and ...
2
votes
3answers
248 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
385 views
Most Effificient Way to Store Ingredients in a Food Database
I have a database that I've called "food" and I'm a bit stumped on how to store the ingredients for a recipe. Should I create a separate table for the ingredients or just have the ingredients insert ...
2
votes
1answer
222 views
MySQL data modeling help
I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach.
One or more organizations can sponsor an event or ...
2
votes
2answers
408 views
How do I properly design a many-to-many (charges/payments) accounting system?
This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance.
Basically I'm having trouble ...
1
vote
3answers
137 views
How to model inheritance of two tables mysql
I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal).
...
1
vote
1answer
209 views
designing server side database to store user-specific data
I have a database with a number of quiz questions and answers. My basic design consists of these two tables
questions (question_id, question_string);
answers (answer_id, answer_string, ...
1
vote
2answers
423 views
Model a family tree database in Access 2007
I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
