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
196 views
How can I design a table for quick versioning of textual data?
Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
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
849 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
525 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
403 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
283 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
517 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
390 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
256 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
172 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
738 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
276 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
404 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
444 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
442 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
220 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
904 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
322 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
258 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
550 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
348 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
542 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
756 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
239 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
295 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
534 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 ...
