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

0
votes
1answer
228 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
101 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 ...
8
votes
3answers
13k 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
742 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
53 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 ...
0
votes
0answers
109 views

please help me connect workbench on 5 system having one server

We have computing software which has one server for 5 computers. I can work on server in MySQL workbench but when i try to work on second computer i get error failed to create ipc port : access is ...
-4
votes
4answers
128 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
108 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 ...
3
votes
4answers
895 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
198 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
16k 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
84 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
415 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 ...
2
votes
1answer
112 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
315 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
163 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: ...
4
votes
1answer
705 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
1k 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
71 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
50 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
489 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
491 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
77 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
116 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
134 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
101 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
144 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
1k 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
201 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
146 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 ...
0
votes
0answers
67 views

In regards to GS1 barcode - what size DB field are used?

In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
6
votes
4answers
3k 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, ...
0
votes
1answer
35 views

Modify legacy table schemas that have no primary key

I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy ...
1
vote
0answers
342 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
1
vote
1answer
452 views

Compare two MySQL dump files

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
4
votes
1answer
415 views

Will table inheritance in PostgreSql suit this situation?

Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL. So my main aim is to have a Main schema in this DB, which I am going to use to ...
0
votes
2answers
962 views

Billing Module (Fee) for School Management System

I am working on a School Management software which includes module for billing of students i.e, fee collection from students. The fee is collected on monthly basis (12 times a year) and the total ...
1
vote
2answers
1k views

In Oracle, how can I give one user privileges to alter the schema of another user?

I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that ...
1
vote
1answer
42 views

Multiligual database design

I assume that the best approach for many languages (5 and more) is concept based on: CREATE TABLE `language` ( `language_id` char(2) NOT NULL, `collation` varchar(64) NOT NULL, ...
0
votes
1answer
860 views

Granting permissions only on a set list of objects

I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in ...
1
vote
2answers
158 views

Extensible Asset Database Schema

Background I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an ...
2
votes
2answers
60 views

Best practice for database where the values can't change

Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The ...
0
votes
2answers
1k views

MS Access ER diagram

I have database scheme for Microsoft Access. I'm interested how I can convert the scheme into an ER diagram? Is there any tool that can do this based on scheme relations?
7
votes
4answers
460 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 ...
1
vote
2answers
1k views

SQL Server - backup / restore schema only (not a duplicate if that's what you're thinking!)

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
2
votes
1answer
2k views

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. ...
2
votes
2answers
197 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
4
votes
2answers
373 views

Restore SQL Server database from .sql files

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git The ...
0
votes
0answers
44 views

Examples of a database with no consistency measures that ensures consistency through schema alone

To clarify, the database can be any type RDBMS, NoSQL/BigTable. However, it must not employ any usual consistency measures such as locks, or synchronization, but the inconsistency-invincibility must ...
3
votes
1answer
309 views

Dump PostgreSQL schema with foreign keys and referenced data

The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema. However according to the same documentation (read Note on ...