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
566 views
Best way to handle dates prior to 1000 A.D. in MySQL?
I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000.
Is there a way that would be more convenient than either ...
4
votes
3answers
388 views
Scalable table structure for periodically updated stats that get aggregated over time
I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later. For example, every 30 seconds I could receive the number of oranges sold at a store in the ...
4
votes
4answers
171 views
Dynamic contacts database
I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories ...
4
votes
2answers
106 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
208 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
72 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
94 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
230 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
319 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
717 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
439 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
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
130 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
564 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
92 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
854 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
294 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
884 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
540 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
122 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
227 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
1answer
1k views
Storing XML in varchar(MAX) or use SQL-XML
I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
4
votes
2answers
357 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
182 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
338 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
105 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
183 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
481 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
111 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
269 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
188 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
3answers
216 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
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
234 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
449 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
252 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
319 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
128 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
145 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
96 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
110 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
638 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
2answers
428 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 ...
