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
vote
2answers
197 views

Oracle - Is it possible to maintain transaction across multiple schemas?

I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower ...
0
votes
0answers
101 views

Save output of MYSQL Schema Inspector

MySQL Workbench 6 has a Schema Inspector that shows all the tables in a DB and useful statistics such as number of rows and average row lengths. How can I get that in a form that I can email someone ...
1
vote
1answer
139 views

Should I extract / normalize the mailing address despite requirements limiting number / type of addresses?

I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn). The requirements for the application are to limit the addresses (and phone / email ...
0
votes
1answer
119 views

Data Sync between servers with different schema in SQL Server [closed]

I have two servers 2008 R2 and 2005. The e-commerce site has been running with 2005 Server i has migrated 2005 server data with modified data schema to 2008 R2 from yesterday's date of data. My client ...
0
votes
0answers
17 views

Services in a Catalog with Year/Week/Day period

I want to create a database structure for the Services in a Catalog with Offres for one or more services. Each Offre is available for one Year period ( example : 01 Janvier – 31 Mai ) , for a Week ...
0
votes
1answer
1k views

Candidate key = Key = Minimal Superkey?

I got a little confused by all these keys. As I understand A key of a relational schema = The minimal subset of a superkey that is still a key = A candidate key Is this correct or not?
2
votes
3answers
358 views

Many to many link tables with history? [closed]

While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to: enable customers to subscribe to multiple services each service can have multiple domain names per ...
-2
votes
1answer
147 views

message table schema - body TEXT or varchar [closed]

I want to make a small social network. For messaging body which one is more suitable? VARCHAR or TEXT? if VARCHAR how long is efficient?
55
votes
5answers
4k views

How can a group track database schema changes?

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

How to reduce table duplication, while ensuring integrity of the database?

For example, in my app I have some class called Contact: case class Contact(name: String, email: String, phone: String) And several other classes have one-to-many relation to that contact (for ...
1
vote
0answers
316 views

Powershell to dump database schema to text file

I want to be able to dump a database schema to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it ...
6
votes
3answers
3k 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 ...
3
votes
5answers
1k 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 ...
0
votes
0answers
26 views

Create a schema like an existing schema [duplicate]

I have a existing schema in my database called SCOTT, how can I create a new schema with exactly the same privileges which SCOTT schema is having? Is there any create like or some command to create ...
0
votes
3answers
62 views

ER Schema “Optimisation”

I am trying to make an ER Schema for a person. The person will have Current Address and Permanent Address. So far I have it so that both Current and Permanent Address are Composite Attributes and they ...
2
votes
1answer
80 views

Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him. I want to insure that the host you assign to the domain belongs ...
0
votes
2answers
315 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
2
votes
2answers
311 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
1
vote
0answers
107 views

Are duplicates ever acceptable in a database

A warehouse system I am working on allows inwards consignments [type = 0] and outwards consignments [type = 1]. The user selects multiple products for each consignment and then multiple warehouse ...
1
vote
1answer
609 views

Schema Change/Update script for Database deploy [closed]

I have a need to change the database schema . I'm planning to write Schema change and update scripts for tracking database changes and updating them. I followed Versioning Databases – Change Scripts ...
0
votes
1answer
110 views

Whether or not to create separate tables

I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to ...
0
votes
1answer
81 views

is there a recommended way to update many postgres schemes

I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions. the system uses one scheme at a time, as if they were separate databases. So ...
0
votes
1answer
314 views

How does security context propagate between schemas within a database?

We have a SQL Server 2008 database that contains two schema's. The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership. I have created a ...
2
votes
2answers
110 views

What is a standard or conventional name for a column representing display order? [closed]

For example, a junction table associating a product and its pictures: create table product_picture ( product_id bigint not null references product(id), picture_id bigint not null references ...
12
votes
3answers
26k 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? ...
1
vote
3answers
871 views

SQL Server 2008 R2 Express Edition - generating database create script

I need to script a database schema out to T-SQL for inclusion source control. The Express version of SSMS doesn't seem to let you script the entire database, just individual tables. Are there any free ...
2
votes
1answer
56 views

How to handle “or” possibilities in MongoDB

I'm new to MongoDB and by no means an expert in DB design in general. This feels like a really basic question that's probably been answered a million times, but I'm having a surprisingly hard time ...
-4
votes
4answers
131 views

Is there a DB that will allow the same field name with different cases?

Is there a DB that will allow a user to create a table with two fields, one calls MYFIELD, the other called myfield?
0
votes
1answer
243 views

Why can't the schema owner create a new table?

I have a DB user called STANMAN which owns the STANMAN schema. When I try to create a table as STANMAN, it says STANMAN has insufficient privileges. How could a schema-owner not already have ...
4
votes
4answers
2k views

Open source tools for Oracle schema compares

I'm looking for an open source tool to generate DDL scripts. Many tools call these synchronization scripts. We essentially want to compare schema of different environments (ie: DEV to QA, QA to ...
0
votes
1answer
270 views

Database schema design help needed

I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance. This application ...
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 ...
2
votes
1answer
147 views

MySQL Performance Gains By Splitting Tables?

I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which ...
2
votes
1answer
120 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
1
vote
1answer
407 views

Creating schema for a shop that sells items from multiple tables

I am trying to create a table in mySQl that contains a list of all the items that a shops. For example: shops (shop_ID (PK), shop_name) shops_and_items (shop_ID (PK), item_ID (PK), price) The issue ...
2
votes
3answers
225 views

Database design for conditional detail table

I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables: ...
5
votes
1answer
2k 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 ...
1
vote
3answers
2k views

Workarounds for setting default schema for domain group?

I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated ...
2
votes
1answer
98 views

Column type unknown at creation time

I have a table in a MySQL database that stores survey results. CREATE TABLE survey_responses ( response_id INTEGER UNSIGNED AUTO_INCREMENT, survey_id INTEGER UNSIGNED NOT NULL, question_id ...
2
votes
1answer
55 views

Strategies for summary data in parent record based on detail data?

I have parent-child relationship as shown below. I need to find the first order date for each customer. Obviously, writing a query to do this is fairly straight-forward but it does require reading all ...
6
votes
4answers
554 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 ...
5
votes
3answers
762 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 ...
0
votes
1answer
92 views

When is it worth it to use Triggers and stored procedures/functions? [closed]

As an example, lets say I have a forum application and for every topic that is created, I have a trigger that fires a database function creating a post.(lets assume we have a topics table and a post ...
1
vote
1answer
133 views

Mysql: Schema/Query Performance Approach for aggregated mailbox folders

I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes: ...
1
vote
1answer
177 views

What is the meaning of this anomaly warning message?

I have run an analysis with SchemaSpy. The tool gave me a warning message for two tables. The warning message is: Tables with incrementing column names, potentially indicating denormalization ...
5
votes
1answer
119 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 ...
0
votes
1answer
199 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
1
vote
1answer
2k views

Oracle database created whats next? Create Schema or Create Tablespace?

I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here. I'm supposed to create a database for an application. ...
0
votes
0answers
262 views

XML Data convert to SQL database,

I have an XML feed that i need to parse daily, i want to store all of the data from the XML feed in a SQL table so then that data can be queried and manipulated by my clients. I can currently parse ...
2
votes
2answers
203 views

PostgreSQL and default Schemas

Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas: Now from my understanding Schemas are like folders for organization, etc. So the ...