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).

learn more… | top users | synonyms

54
votes
5answers
3k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
36
votes
5answers
3k 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 ...
19
votes
3answers
25k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
17
votes
1answer
4k 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 ...
13
votes
4answers
8k 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 ...
11
votes
3answers
3k 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
2answers
389 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 ...
10
votes
5answers
3k 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 ...
10
votes
2answers
921 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 ...
10
votes
3answers
19k 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? ...
9
votes
3answers
365 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
4answers
732 views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
7
votes
4answers
873 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
1answer
511 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 ...
7
votes
4answers
556 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 ...
7
votes
2answers
309 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
1answer
3k views

What is the usage of performance_schema in MySQL 5.5?

I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list. What is the usage of this new schema..? How can we monitor using this schema..? What are the ...
7
votes
2answers
1k 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 ...
7
votes
1answer
265 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 ...
6
votes
6answers
5k views

Is there any good tool for making Database Design and Prototypes? [closed]

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 ...
6
votes
2answers
341 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
512 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
4k 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
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
3k 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?
6
votes
3answers
2k 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 ...
6
votes
2answers
697 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 ...
6
votes
3answers
1k views

Is there an Open Source Metadata Management Solution? [closed]

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 ...
6
votes
1answer
978 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 ...
6
votes
1answer
1k 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 ...
5
votes
6answers
418 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
3answers
813 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
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 ...
5
votes
1answer
107 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
5
votes
2answers
614 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
104 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
483 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
3answers
595 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
235 views

Searching 500 million records of binary data

I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read ...
5
votes
3answers
1k 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
722 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 ...
4
votes
2answers
338 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
777 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
4answers
2k views

Are there any free online collaboration database schema tools? [closed]

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
3answers
6k views

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the ...
4
votes
1answer
296 views

How to migrate between schemas in bulk in SQL Server?

We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas. In MS SQL Server 2008 R2, how can I relocate all the contents of one ...
4
votes
1answer
185 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
2k 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
442 views

Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't ...
4
votes
1answer
816 views

How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...