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.
4
votes
2answers
93 views
Foreign keys - link using surrogate or natural key?
Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
4
votes
1answer
201 views
Best way to index a table with two datetime columns that are equally queried
Sql Server 2008 R2.
I have a table with ~70m records, about 10 inserts per second.
It's currently clustered on a CreatedAt datetime column, which always increases. 50% of queries involve this ...
4
votes
1answer
71 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
93 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
1answer
229 views
Database structure, relational or data warehouse?
Good morning,
Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns:
Date ...
4
votes
2answers
306 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
689 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
418 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
1answer
424 views
LDF and MDF file sizes for a dynamically created Database
I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. ...
4
votes
3answers
1k 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
128 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
1answer
85 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
833 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
2answers
264 views
free database modelling software
What is available in the realms of database modelling software that is free. I am developing an SQLExpress database.
CA ERWin - community version
although is free, I can't see an option to allow ...
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 ...
4
votes
3answers
2k views
PostgreSQL designing tool
I am about to design my first database which is going to run with PostgreSQL. I am used to the magnificant tool called MySQL Workbench. It is useful and it looks good which I kind of expect from a ...
4
votes
2answers
6k views
Relational Design - Multiple tables into one foreign key column?
Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was.
But, the different types of meat are unique in some way (such as USDA ...
4
votes
3answers
530 views
Embedding vs foreign table in one-to-one relations?
I'm currently taking a university course about databases.
Consider the following modeling scenario where a car exists only in the context of its owning student:
Student (0,1) <---> (1,1) Car
...
4
votes
2answers
121 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 ...
4
votes
1answer
215 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
348 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
3answers
1k views
Are there any free online collaboration database schema tools?
I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
4
votes
1answer
178 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
1k 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
2answers
2k 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
3answers
329 views
Should I seperate frequently updated columns
I have a users table that contains users' information and a column named credits that is frequently updated.
On index page I'm showing list of users with basic user information but I don't need ...
4
votes
1answer
103 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
1answer
168 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
1answer
440 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 ...
4
votes
1answer
107 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
2answers
260 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
3answers
187 views
How do I add a time dimension to this forecasting database without having to duplicate every data point @ each reforecast?
I have a database designed to facilitate revenue forecasting. Revenue forecasting consists of tracking Orders which are recognized into Revenue at multiple points in time (each, a revenueDate). There ...
4
votes
1answer
54 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
1answer
226 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
3answers
426 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
1answer
251 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
3answers
307 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
1answer
121 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
131 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
91 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:
...
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" ...
4
votes
1answer
109 views
Beginner design of biological sampling database
As a complete newcomer, I would like to practise designing a database based on the biological data my lab has been accumulating over the years. Here is an explanation (I am simplifying/abstracting ...
4
votes
3answers
620 views
How do you structure a database that organizes post by location like craigslist?
I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to ...
4
votes
3answers
207 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
2answers
396 views
How to implement soft deletes?
What is your method of implementing soft deletes in a database?
Ideally the solution would allow for
decent performance on large tables
take relationships into account
take unique keys into account
...
4
votes
1answer
95 views
Possible pitfalls of condensing 2 SQL Server instances into 1
I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server ...
4
votes
2answers
566 views
What are some common and useful fields for a users table in a database?
I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, ...
4
votes
1answer
47 views
How to link Similar-but-distinct Models of Medical Imaging Data?
I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two ...
4
votes
1answer
233 views
What databases or styles of database can use git (or another dvcs architecture) to manage cross-site replication?
I'm looking for a database software package for a dictionary (like the hunk(s) of dead tree that you might have somewhere at home). I would like the database to
scale well to a million key-value ...
4
votes
2answers
278 views
How do I store data with a query that's approximated?
I'm trying to find a way to store my data with fast access (better than O(n)).
My database consists of data (4096 byte strings) that represents some information about some items.
The problem is, that ...
