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 (1)

2
votes
3answers
801 views

Physical Address Schema

I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked). Store the address ...
1
vote
1answer
950 views

Replicate/Mirror SQL Server schema to slave databases

I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database. MASTERDB will be always empty, as I only need the schema. No data is going to ...
20
votes
4answers
15k 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 ...
0
votes
1answer
166 views

What are the first necessary actions needed to help secure a database's schema & data? [closed]

This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions. It seems like there should be a good checklist out there for all to agree on ...
8
votes
3answers
4k 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 ...
0
votes
3answers
946 views

How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?

I want to design a db schema concerning a schedule. I. I can use all fields in the same table, for example: id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su 1 | 1 | ...
1
vote
1answer
302 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
1
vote
4answers
7k views

Can't create user in SQL Server

Hi guys when I try to create user for my database I always encounter this kind of error. This is the sequence of task I done. I manage to create new login using windows authentication of SQL then ...
9
votes
3answers
2k 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 ...
1
vote
1answer
497 views

schema shows through standard client connection but not through ODBC connection?

Hoping you can help! The Gist When I log into an Oracle 11g DB using SQL Developer or DBVisualizer, I can see schemas and execute queries against them. However, when I use the same credentials to ...
5
votes
4answers
4k 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?
8
votes
1answer
6k 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 ...
8
votes
1answer
758 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 ...
-1
votes
2answers
567 views

Diet and exercise schema

Pardon the brevity but: I am currently working on a project which requires users to create a 12 week diet/exercise regime. I basically need to store 3 meal options for each day which can be made up of ...
2
votes
3answers
634 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
5
votes
1answer
3k 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
483 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 , ...
3
votes
2answers
26k 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
387 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 ...
4
votes
5answers
2k 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
3k 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
467 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
484 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
181 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
2k 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 ...
4
votes
2answers
750 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 ...
8
votes
4answers
1k 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
819 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 ...
15
votes
3answers
7k 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, ...
4
votes
2answers
6k 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 ...
2
votes
1answer
261 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
990 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
501 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
526 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 ...
8
votes
4answers
6k 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 ...
10
votes
4answers
6k 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?
4
votes
1answer
1k 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
3k 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
555 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 ...
15
votes
6answers
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 ...
11
votes
6answers
18k 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 ...
5
votes
3answers
903 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 ...
1
vote
2answers
704 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 ...
8
votes
3answers
3k 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
2answers
6k 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 ...
10
votes
3answers
421 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
575 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 ...
8
votes
2answers
2k 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 ...
60
votes
5answers
6k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
7
votes
1answer
130 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...