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)

16
votes
2answers
939 views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
5
votes
2answers
706 views

Searching 500 million records of binary data

I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read ...
1
vote
1answer
215 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
7
votes
2answers
15k 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 ...
28
votes
1answer
10k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
4
votes
1answer
288 views

Relation between multiple hierarchical taxonomies linked to a single entity

I am working on an online test application which supports multiple choice questions. I have created schema for taxonomy (classification) system to be used with questions. taxonomy(id, name, slug, ...
3
votes
2answers
1k views

Is it possible to get schema ID in another database without using dynamic sql?

I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic ...
3
votes
2answers
340 views

Database Schema for Blank Database is large. 20MB

Is it normal for blank databases with just schema to be large? Or is there a way that I can reduce the size of the initial database?
4
votes
1answer
14k views

SQL Server: grant select access to a user in a view and not in its tables

I have a SQL Server 2012 instance with a few databases. In one of them I created a view, that selects tables in more than a database. I want a user to be able to select that view, but it must not ...
1
vote
1answer
86 views

Permission in schema for user

I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?
3
votes
1answer
727 views

ER vs database schema diagrams

when i search the web for er diagram and database schema these 3 diagram types found but all are named under er diagram.i want to know what is database schema diagram and what is er diagram why all ...
0
votes
1answer
143 views

Read-only user query in Oracle

I have been asked to create a read only user in a Oracle database 12c. The read only user must have read only grants only to the objects of select schemas, which I created and handed over. But now ...
0
votes
2answers
1k views

In Oracle, how to get a list with the names of schemas that a user has read permission?

In Oracle, i need list which schemas a particular database user (or the current database user) has read permission. Based on this information, also need to get all the tables and views are in each ...
1
vote
0answers
47 views

Design options for time series scientific data

I've been scratching my head over the last few days trying to find a solution that best suit my needs. I tried to think that what I want is probably not very different from what many others wanted ...
0
votes
0answers
27 views

Help creating database schema to build database off

not sure if this is the right area for a question such as this but I am looking to create a database for an IS warehouse who deliver laptops, desktops, monitors and other peripherals to different ...
3
votes
1answer
55 views

Seeking feedback on table relationships, general schema design

I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL. Here's what I have so far, link to full size image I'...
0
votes
0answers
55 views

Database schema for storing edits in the content [duplicate]

I want to store all the edits of a specific table. Multiple fields can be modified and for different users. I want to store all changes, taking in count that the number of fields that can be edited ...
1
vote
2answers
168 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item <...
7
votes
1answer
533 views

Efficiently storing sets of key-value pairs with wildly different keys

I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all ...
1
vote
1answer
33 views

SQL Server 2008 running schema query without identifiable cause

I am analyzing the performance on login to an application and in particular making sure the database response is acceptable. In running just the simple Activity Monitor I can see that there are two ...
0
votes
1answer
36 views

Can I create a unique constraint across schema in Postgres?

I have a task that I am trying to accomplish where the email address is unique. The email address is stored in the a3.user table as user_name. CREATE UNIQUE INDEX ix_user_user_name ON a3."user" ...
1
vote
2answers
5k views

DB2 - how to safely blow away database on linux?

I'm using (I believe) 10.1.2, on CentOS 6.5 (for sure). The underlying problem I have is that the database upgrade package prepared in house isn't working for me, in at least the sense that the ...
0
votes
1answer
22 views

How to find functional dependancies from a relational schema?

If only given a schema (table header, with primary key indicated), how do i find functional dependancies? My problem is that would not functional dependancies depend on the data stored int he table, ...
1
vote
0answers
23 views

UML database schema based on the creteria

I have the following requirements to design a database schema (brief): A Graphic Designer (we will call them designer) gets a call from a client with a new job to design a logo. The designer logs-...
1
vote
2answers
62 views

Why not have Persons for every person in schema?

I noticed that a lot of database schemas often show like, "students" or "teachers" or "employees" or "passengers" or something to that affect. Why not have a schema where every individual is a ...
0
votes
1answer
36 views

MongoDB storage of multi-dimensional arrays

Using MongoDB 3.0.7 as the primary datastore. I connect to it using ReactiveMongo 0.11 on a JVM-based language. My question is: how well does Mongo store multi-dimensional arrays? For image analysis, ...
36
votes
5answers
89k 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? ...
3
votes
2answers
2k views

Schema Migration: SQL Server Data Tools vs Liquibase and Flyway

This might seem like a stupid question, but I've been looking into open source solutions for schema migration, namely Liquibase and Flyway. However, my boss told me that SQL Server Data Tools (SSDT) ...
0
votes
0answers
76 views

Hard dba case [schema to store delivery prices for shops]

I want to make a database scheme which would allow me to fetch the delivery price for the different Shops basing on the delivery destination (Address/Region) and order value (price). Here is the ...
1
vote
1answer
48 views

Active Directory group as a schema in SQL Server

In SQL Server 2008 R2 we have a database that is being maintained by a team of developers. Each of them should have permissions to perform DDL operations such as adding a column or creating a table. ...
1
vote
1answer
81 views

SQL Schema Change; Modification of Existing Table vs Creation of New

We are planning the data schema for our project. Currently we have a table with classifications, but there is the possibility of a new classification in the future which would require a new field to ...
1
vote
0answers
25 views

DATE - Normalization, a multiple ranged query, multiple inserts and multiple update

I have a booking system, airbnb style. TABLES -> prices, house, available_date A house has available days and not available days, but sometimes the days have different prices. A weekend is more ...
0
votes
1answer
173 views

How would you design a table for approval system between User and Sports Club

I am currently learning database modelling and struggling to model the following requirement. I have a user and sports_club table. User can only join a sports_club if three existing users of the club ...
0
votes
1answer
49 views

Compare order of columns between two databases

I have two postgres databases which should have an equal db-schema, but have not. I want to see the difference in the column order. Columns of table1 in Dev: id, foo, bar Columns of table1 in Prod:...
0
votes
1answer
126 views

Single-Table vs. Multi-Table design

I'm trying to come up with a database schema which is going to be used for a social network similar to that of Facebook. Now for posts I have two schemas in mind which are as follows: Single-Table ...
0
votes
1answer
43 views

Default schema is ignored on SELECT

According to my understanding, a default schema should apply when issuing SQL statements on tables using unqualified table names. My problem is that the following statement fails, because the table ...
2
votes
2answers
257 views

Schema to make a Payment History/Viewing History

On some websites I have seen two history of events: Payment History and View/Watched History. I want to have both payment and viewing histories in my app, but I got stuck when I started thinking ...
0
votes
0answers
96 views

How can I layout a schema that will track monthly installments?

I'm pretty new to database design and have a beginner's understanding of SQL, mostly simple SELECT statements. I find myself in a situation where I have to create a system that will track monthly ...
0
votes
0answers
22 views

How can I organize this data?

I have been working in a system but don't know how to represent some of the data and its relations. There are three warehouses and a four shops working with the system and until this moment we can ...
0
votes
0answers
95 views

Finding the closure and super keys of a schema?

I am working on a problem in which I have to look at the relation R with schema R(w, x, y, z), and the three functional dependencies: w -> z ; x,y -> w ;z -> y For every non-empty subset X of ...
0
votes
1answer
55 views

How do I host and separate multiple projects on one Oracle Database?

From Difference between database vs user vs schema I'm trying to understand the concepts of the schema, user and database. My current understanding is you cannot create a schema separately. You can ...
5
votes
3answers
8k views

database schema design for school system

I am trying to create a database to store information about a school. I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes ...
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 ...
3
votes
3answers
2k 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
0answers
72 views

SQLite reference table question

I'm using SQLite and Python for making a sensor network database. I have a table holding the nodes: NODE, another 2 tables for sensor specification: SENSOR_TYPE ("Temperature, Humidity...) and ...
0
votes
0answers
73 views

How to design relationship for racing team trades?

I'm working on a fantasy racing team project. I'm having trouble laying out the schema when it comes to the team table and the trades. I think I have the race, driver, and result tables laid out okay: ...
3
votes
2answers
4k views

Error 42P01: relation does not exist

I'm new at creating databases, and this error has me dumbfounded, as I am super new with DB admin things (I mostly do reporting type queries). I created a new database through pgAdmin3 GUI, and I'm ...
1
vote
1answer
253 views

Referral table structure

I need to make a simple referral system and I have a database table set out as follows: mem_id | date | refer_email | accepted And the meaning of such columns is the following: mem_id: The ...
0
votes
0answers
18 views

custom field related to usergroup and products

We need to create dynamically custom fields and store values related to each user or product in database we have product table related to category table and also user table related to usergroup and ...
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 ...