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

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 ...
4
votes
2answers
341 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 , ...
2
votes
2answers
19k views

SQL Server 2008: How do I change the default schema of the dbo?

When I attempt to change the default schema of the dbo in SQL Server Management Studio, the ALTER fails and states that the dbo account's schema cannot be changed. Are there any tricks that can make ...
0
votes
1answer
332 views

How SQLite index ID (or line number)

SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line ...
3
votes
5answers
896 views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
4
votes
1answer
2k 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 ...
1
vote
1answer
357 views

Is it possible to fetch or update table data by table field type?

Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.
1
vote
1answer
307 views

General Question on how to setup db's for a social networking site

I am currently responsible for designing the entire database setup for a start up website. It is quite a responsibility and I want to get it right, not only for the site, but for my own experience. ...
2
votes
1answer
167 views

Change tables from one user to another

I created some objects in MS SQL Server 2008 using SubSonic 3. It created them based on the user specified in connection string like: test_user.table1 but for some reason I am not able to test_user ...
5
votes
3answers
849 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 ...
3
votes
2answers
539 views

What are the possible considerations and drawbacks of storing binary image data directly in a table?

Although related to this question, I have a slightly different angle to approach this issue from. Here is my situation: I am writing a web application (either in PHP or Python) that manages addons ...
7
votes
4answers
894 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 ...
2
votes
2answers
616 views

PostgreSQL: Separate tables vs single table to perserve disk space?

I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
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, ...
2
votes
2answers
4k views

SQL Xml returning empty root tag on empty record set

I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty node when ...
1
vote
1answer
232 views

SQL Server Schema Security

My company uses a multi-tenant database (one database, all tables have a customerID field). We are in the process of setting up a data warehouse and giving clients direct access. Our initial ...
1
vote
1answer
484 views

Is it possible to make schema changes on a replicated subscriber database?

I have a table that is published via transactional replication. A developer has requested modifying the subscribed table to add a column that is to be used for reporting purposes. The column will be ...
7
votes
2answers
314 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 ...
3
votes
2answers
408 views

Unique time range or database schema for a scheduling system

I'm having to design a table which contains scheduled times for media to be displayed. These scheduled times should not overlap. In my SheduledSlot table I have: id integer PRIMARY KEY begin ...
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 ...
7
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?
3
votes
1answer
871 views

SQL: transfer database schema

If i want to transfer schema ownership to another schema i know that i need to use something like: ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname; But how is it different from: ...
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
427 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 ...
11
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 ...
7
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 ...
4
votes
3answers
721 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 ...
0
votes
2answers
479 views

How wrong is my web app modeling schema?

So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years ...
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 ...
4
votes
2answers
3k 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 ...
9
votes
3answers
368 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 ...
3
votes
3answers
484 views

Storing a large number of small size entries in MySQL

I have a database that stores readings from numerous gauges. Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 ...
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 ...
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?