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.
5
votes
1answer
400 views
What makes an OLTP database an OLTP database?
This is a pretty broad question. I know the definition of OLTP. But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that ...
5
votes
2answers
847 views
Database Hardware and Software to handle million requests per second?
I'm curious about the necessary hardware and software requirements to support a strong MMORPG. For example, what kind of requirement (both hardware and software) would I need if I were to build a ...
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 ...
5
votes
3answers
2k views
Visio 2010 and SQL 2012 - Reverse Enginnering
We have upgraded to SQL 2012 and Am trying to reverse engineer our Dbs into Visio 2010. I setup a connecting using SQL 11 driver and Visio complains that that driver is not supported.
Any one run ...
5
votes
2answers
231 views
Best design for adding a folder/file relationship
I was given a database that has Projects and Media_files. They were in a simple 1 to many relationship.
I now need to add Folders to Projects. Media_files can be placed in Folders, and Folders ...
5
votes
2answers
524 views
How to structure a model to properly and efficiently represent tree-like data on relational databases?
Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical ...
5
votes
3answers
76 views
How can a database be normalized when an optional field causes denormalization?
Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this:
A ...
5
votes
3answers
401 views
What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? [duplicate]
Possible Duplicate:
Files - in the database or not?
I've been reading "Programming Entity Framework Code First" ...
5
votes
1answer
279 views
Denormalized table designs in normalized database?
There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
5
votes
2answers
144 views
Help with good RDBMS transactional schema design that mimicks a sports league
I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are:
Well normalized
Transactional
Application ...
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
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
2answers
514 views
Database redesign opportunity: What table design to use for this sensor data collection?
Background
I have a network of approximately 2000 sensors, each of which has about 100 data points that we collect on 10 minute intervals. These data points are typically int values, but some are ...
5
votes
2answers
389 views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
5
votes
2answers
94 views
Per person weighted answers
Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits.
My question is I have a set of people for which I want to record needs mapped to ...
5
votes
2answers
186 views
How should I model an “either/or” relationship?
Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has ...
5
votes
2answers
82 views
Relationship that are optionally more specific
Forgive my title, I couldn't think of anything that accurately describes what I'm talking about.
I currently have the following relationship.
CREATE TABLE Events
(
ID INT IDENTITY(1,1) NOT ...
5
votes
3answers
255 views
How to store many smallish time-series in a relational DB?
Input Data
Multiple Tests Beds generate measurement data of various complexity.
In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
5
votes
2answers
189 views
How should an object that can reference an object of multiple types be modeled in a relational schema?
Apologies for the obscure question, it may make more sense with a concrete example:
In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
5
votes
1answer
434 views
Can filtered indexes help improve queries that are based on a time entered or should this be avoided?
I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help.
I can see that adding a filtered index to an employees ...
5
votes
1answer
71 views
Database Design: Linking 'vague' entries to associations?
Experienced developer, inexperienced DBA here.
I'm currently working on a project that lets users input the names of freight vehicles for particular packages. Eg:
Company: White Star Line
Vehicle: ...
5
votes
1answer
170 views
How to store and query products the price of which depends on a complex set of conditions?
I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB.
Let the products be packages of cable tv providers:
{
name: "Virgin TV",
price: 20
},
{
name: ...
5
votes
2answers
734 views
how to enable temporal database capability in mysql.?
How to enable the temporal database compatibility in mysql database?
Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
5
votes
1answer
90 views
To Foreign Key or not to Foreign Key… (inet -> cidr)
Below is a very abridged version of a project I am working on.
CREATE TABLE cidr_block (
id serial not null unique,
block_def cidr primary key,
dhcp_server_id int not null references ...
5
votes
3answers
271 views
Partition pruning with multiple date columns
I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
5
votes
2answers
399 views
Vehicle spare parts inventory datadase model
I am modeling a database to be used for vehicle spare parts inventory management and I am stuck...
Some spare parts can be broken down from a kit into component parts. I would like to create a model ...
5
votes
4answers
181 views
Database Design for updatable sequential records
I am working on creating a data model for storing data related to production tracking. I work for an engineering firm that models and analyzes data for our clients. There are several steps in the ...
5
votes
1answer
272 views
Design for file based with many events
I'm not a database guy but I need to design a database to store events for our application. Our client app connects to remote hardware and syncs count data. The count data are csv files split into ...
5
votes
1answer
443 views
How to handle “reference tables” in CouchDB?
What's the best way to handle "reference tables", tables that contain static data, for example a list with various kinds of ingredients for food?
Multiple documents, one for each ingredient
One ...
5
votes
1answer
441 views
In MongoDB, is having lots of small collections better than having one huge collection?
Huge collections have huge indexes that may not fit into RAM. Is that problem entirely solved with sharding ?
If I have lots of small collections with small indexes, what are the options for storing ...
5
votes
1answer
219 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
4
votes
8answers
1k views
Should you ever not assign an unique ID to each row in a table? [duplicate]
Possible Duplicate:
Should every table have a single-field surrogate/artificial primary key?
I can't imagine ever not using a unique ID with every table, which is why I'm asking this ...
4
votes
6answers
903 views
Any way around unique index 16 column max
According to the CREATE INDEX documentation:
Up to 16 columns can be combined into a single composite index key.
We've got a table with ~18 columns that need to form a unique combination. This ...
4
votes
5answers
2k views
Pros/Cons Using multiple databases vs using single database
Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented.
While I don't agree I'm having trouble collecting good ...
4
votes
2answers
216 views
Introducing an 'age' category in a patients database
I want to do the following in my DB related to patients:
when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
how ...
4
votes
3answers
321 views
Database Design Issues
My question consists of three parts:
When can I be sure that my database design is perfect?
Is returning to the database design to change some issues (i.e, adding new column, deleting a column, ...
4
votes
1answer
257 views
Is that a good idea to use one database for 50.000+ shops?
I know Shopify use only one database for all shops.
But how can they handle their database with such big data?
Is that a good idea to use one database for 50.000+ shops?
4
votes
4answers
217 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 ...
4
votes
3answers
541 views
How to have a one-to-many relationship with a privileged child?
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
4
votes
3answers
346 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
4
votes
4answers
540 views
Speed problem with a simple select statement
I'm having a problem with a select statement taking way too long to run. I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 ...
4
votes
2answers
755 views
Data validity and accuracy according to database integrity
What does it mean that data is valid and accurate according to database integrity?
4
votes
2answers
280 views
Best way to design this mileage table?
I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source.
It's currently set up as so:
id (primary_key)
, truck_num
, start_date
, end_date
, ...
4
votes
2answers
232 views
Is it ever a good idea to denormalize for integrity?
I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers.
These problems can take a variety of forms - they might be multiple ...
4
votes
4answers
2k views
Database design for a time tracking application
I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he ...
4
votes
2answers
293 views
Are there any tools to go from ERD to DB design and vice versa?
I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa.
For the record, the database i'm using is MySQL.
4
votes
2answers
234 views
Clustered indexing is now must - why?
Earlier, there were non-conclusive to me debates/discussions on whether to (always) engage/avoid clustered indexes.
Well, I understood that they are to be used sometimes with proper + specific ...
4
votes
2answers
530 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
378 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
168 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 ...
