A schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables).
49
votes
5answers
2k views
How can a group track database schema changes?
What version control methodologies help teams of people track database schema changes?
31
votes
5answers
1k views
Is there a name for this database structure?
Noob question, sorry if it's too basic for this exchange. We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one ...
12
votes
1answer
2k views
Best practices for schema changes and data migrations to a live database without downtime?
How do you make schema changes to a live database without downtime?
For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
11
votes
3answers
2k views
Schema design - best practices?
I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
10
votes
4answers
4k views
Should dbo schema be avoided?
When it comes to the dbo schema:
Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own ...
10
votes
5answers
2k views
Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project
I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema ...
9
votes
2answers
283 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
9
votes
3answers
306 views
How-to implement an entity with an unknown maximum number of attributes?
I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
8
votes
2answers
610 views
Schema-less/flexible + ACID Database?
I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be ...
7
votes
4answers
714 views
Guidance for using composite keys to identify rows
Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting ...
7
votes
2answers
244 views
Can I losslessly decompose this table?
I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills.
In essence, I have a table with the following primary key:
child_id integer
...
7
votes
2answers
629 views
Schema to support dynamic object properties
I'm working on an editor that enables its users to create "object" definitions in real-time. A definition can contain zero or more properties. A property has a name, a type and possibly a default ...
6
votes
2answers
253 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
361 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 ...
6
votes
4answers
1k views
Is there a tool to check if my database is normalized to the third normal form?
I learned about normalization recently, and understand how important it is when implementing a new schema.
How can I check if my database is 2NF or 3NF compliant ?
Manual review is a sure option, ...
6
votes
1answer
368 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
6
votes
3answers
2k views
How to version control PostgreSQL schema with comments?
I version control most of my work with Git: code, documentation, system configuration.
I am able to do that because all my valuable work is stored as text files.
I have also been writing and dealing ...
6
votes
4answers
170 views
Database Design - different objects with shared tagging
My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the ...
5
votes
6answers
328 views
Running a database without constraints
We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other ...
5
votes
5answers
2k views
Is there any good tool for making Database Design and Prototypes?
I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
5
votes
4answers
2k views
Finding empty columns of a table in PostgreSQL
What query would return the name of the columns of a table where all rows are NULL?
5
votes
3answers
554 views
Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?
Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
5
votes
2answers
872 views
Is there an Open Source Metadata Management Solution?
Is there an Open Source Metadata Management Solution? I'd like to create a Metadata Repository that will hold the details of the metadata of database schemas, tables and data items of hundreds of ...
5
votes
2answers
308 views
How recording of every change of a row in a database is generaly stored?
In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
5
votes
2answers
641 views
Difference between database vs user vs schema
I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?
If they are same then, what are the similarities between them? ...
5
votes
2answers
85 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
1answer
205 views
Converting Database from GUID to Integer Keys
I have a medium sized database (~100 tables) that uses GUIDs as primary keys in all tables.
In one of our most active tables the GUIDs are causing an unnecessary increase in storage requirements. ...
5
votes
1answer
490 views
Do schema changes “break” SQL Server 2012 AlwaysOn or are they handled transparently?
My organization is planning to adopt SQL Server 2012 AlwaysOn and I am trying to understand what impact (if any) it will have on our application upgrade process.
We release application updates on an ...
5
votes
3answers
274 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
3answers
318 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
446 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
1answer
921 views
How to design a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)? [closed]
I'm building a site ("The Site") and I'm considering how to design the DB schema for it.
I have a few scenarios that will affect the outcome:
The user can choose to register a native account on the ...
4
votes
2answers
288 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
347 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
1k views
PostgreSQL: Creation date of a table
I've recently finished a project, during which many DB tables were created.
Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.
Is there a ...
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
855 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
1answer
2k views
SQL Server: hierarchy of permissions for schema?
We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is ...
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
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
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
1answer
4k views
How to transfer data using expdp and impdp commands?
I'm Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump expdp and impdp commands. I have ...
4
votes
2answers
263 views
Restore SQL Server database from .sql files
So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git
The ...
4
votes
2answers
412 views
What's a good relational structure for units and complex unit conversions?
My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
4
votes
3answers
631 views
Need database of categories for products and services
Need to develop a database schema that associates products and services into industry-standard categories. I know that both the schema and data are out there, yet I'm not finding it.
There are ...
4
votes
1answer
288 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 ...
4
votes
1answer
170 views
Allow user to do anything within his own schema but not create or drop the schema itself
I have created a schema in SQL Azure and granted the following permissions to a database role:
CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';
CREATE SCHEMA myschema ...
4
votes
1answer
100 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
3
votes
1answer
382 views
noSQL ACID and consistency for banking
We are building a real time banking platform and app.
Our prime concern is safety and consistency of our data across multiple db servers. We need transactions and we can't afford to risk power ...
