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
1answer
72 views

Multi tenancy by schema - Deny access to another tenants [closed]

In a multi tenant environment, where the multiple tenants are in the same database and a schema is used to separate each one of them, how can I create a database user (MS SQL SERVER) for each schema, ...
0
votes
0answers
40 views

Should I be worried about duplicate entries in the Oracle all_dependencies table?

I think there are some problems with object relational functionality in Oracle 11g (and some earlier versions). We have a purely relational mapping ready as it is easier to handle, but need to support ...
1
vote
0answers
68 views

Schema for yearly data models

I am developing a school management system. Most data will not be reused after the end of the school year. I am contemplating using schemas to separate data in various school years, however I am ...
2
votes
3answers
237 views

Database design for bookmark system

I am creating a bookmark system where people can save their bookmarks. Each user has its own personal page with bookmarks. A bookmark should have atleast one tag and max five. Each user can create ...
4
votes
2answers
533 views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
0
votes
2answers
64 views

Store extra, optional information about any entity [closed]

I've done a bit of reading but haven't found anything related. This must be widely discussed, so it's most likely my fault. I've got a relational database with a number of different tables, each ...
1
vote
1answer
193 views

Polymorphic Association - is it bad? [duplicate]

In the following schema: Collections Upvotes Reports Upvotes Reviews Upvotes I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the ...
4
votes
1answer
469 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 ...
0
votes
2answers
93 views

Accept null value on parent column in multipart relationship

EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer. ...
2
votes
1answer
111 views

To normalize or not to normalizde? (10 fields)

I'm helping someone to design a database and for a specific case I'm not sure. I have 10 fields - this will never change - that will always be filled and used in every query that could be coming up. ...
1
vote
2answers
248 views

How to create a blank version of a PostgreSQL database

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my ...
1
vote
2answers
141 views

Postgres sequence usage

I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly. They both have the same objects unshared for tables, however, sequences are shared, ...
1
vote
1answer
216 views

How To Hide Schema from user

I have DB with 2 schemas: A and B. B schema's procedures use A schema's procedures. I need to create user, which will not be able to see A schema. This user can use only B schema, but i want to let ...
1
vote
0answers
77 views

Cloning Postgres schema/tables across multiple servers

I'm working on a project that has databases across multiple servers, with one server for each business client. The project has some identical 'base' tables that are present in all the databases (for ...
0
votes
1answer
204 views

How to transfer the ownership of the dbo schema in SQL Server 2012

I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot: Transfer ownership to another user Drop the user from DB (though I can ...
0
votes
0answers
94 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
2answers
182 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 ...
1
vote
1answer
125 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
0answers
15 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
111 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 ...
2
votes
3answers
332 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
140 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?
0
votes
1answer
87 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
2answers
173 views

How can I integrate a new system to a design scheme I previously created?

This is a follow-up to a question I wrote a while ago, for reference, you can look it up here. I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown) Now that a ...
0
votes
2answers
444 views

Multi-Tenant (SaaS) Database Schema with Sharing

Guys I just need your opinion which is the best approach to design a schema of a database for a SaaS application. I want it all on a single database, At first I think I can simply put a context_id ...
2
votes
2answers
1k views

Import Oracle schema data without losing modifications of stored procedures

I have this scenario: One huge (thousands of tables), complex production database, Oracle 8 One huge (thousands of tables), complex development database, Oracle 9 ( same structure as production ) ...
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
58 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
76 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
287 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 ...
3
votes
2answers
523 views

DB2 (LUW) equivalent for Oracle “drop user cascade”

This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows) On oracle, we have an os user (on ...
2
votes
2answers
286 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
506 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
104 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
80 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
299 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
109 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 ...
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
200 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 ...
2
votes
1answer
135 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 ...
1
vote
0answers
607 views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
0
votes
1answer
630 views

What's a good way to model user authorization to hierarchical data?

I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012). We're working with farm production information. We will have users who need to be authorized ...
1
vote
1answer
384 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
210 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: ...
1
vote
1answer
536 views

Oracle schema migration to new database with zero downtime

I have a problem: I have two production schemas on one database each serving two different appplicaitons. I need to migrate (move) one schema to an entirely new database that will be dedicated for ...
2
votes
1answer
95 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
6k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...