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)

0
votes
0answers
37 views

Why default db privilleges are not applied on a new schema?

I have a user with all privileges granted by default in database properties. I create a new schema as postgres and table there. I expect that the user should have an access to this table but it looks ...
0
votes
0answers
63 views

create table in a different schema

I am trying to create a table on a different schema without using "create any table" privilege. I have an application user/schema. TABLESPACE_NAME USEDMB MAXMB ...
0
votes
1answer
27 views

Every “INSERT” query check a combination of column's records

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema. What i want? In a simple table, for instance Table1, there are two different columns, Series and ...
0
votes
0answers
24 views

Schema Design for multiple many-to-one relationships. of which exactly one should exist

Say I have table A, B, and C. B and C can both be related to multiple A's, but each A should have exactly one of either B or C related to it. Which database design is better: A: a_id, b_id NULL, ...
1
vote
1answer
66 views

How can I create a MongoDB based on MySQL schema?

How can I create a MongoDB based on MySQL schema? This is my schema: productCode VARCHAR(15) productName VARCHAR(70) productLine VARCHAR(50) productScale VARCHAR(10) productVendor VARCHAR(50) ...
1
vote
1answer
39 views

Normalizing a folder schema

I've created a schema to allow creation of folders for customers. Customers Id Firstname Lastname StreetLine1 StreetLine2 City CustomerFolders Id CustomerId ParentId (NULL) Name Now as you can ...
0
votes
1answer
50 views

Database design for actions on some entity

I am making a todo list kind of a service. One todo list will have several items , that can be done/deleted/snoozed etc. Users can also add a new item to the list , delete an item etc. I want to ...
3
votes
1answer
61 views

What are some databases that are schema-full but not relational

What are some databases/datastores that are schema-full, but not relational? In other word I'm after some NoSql databases that are not schema-less. By schema-full I mean that, like in a traditional ...
79
votes
4answers
119k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
2
votes
1answer
68 views

How unique are MongoDB _id fields?

I am trying to create a schema for documents and collections for a MongoDB database I am creating. I have read a few documents describing the _id field that is generated by each document upon ...
4
votes
2answers
215 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 ...
2
votes
2answers
3k views

Import Oracle schema data without losing modifications of stored procedures

I have this scenario: One huge (thousands of tables), complex, Oracle 8 production database, One huge (thousands of tables), complex, Oracle 9 development database, (same structure as production) ...
5
votes
4answers
11k views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two ...
16
votes
2answers
877 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
659 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
180 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
13k 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 ...
27
votes
1answer
9k 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
273 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
337 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?
2
votes
1answer
11k 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
66 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
447 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
95 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 ...
3
votes
6answers
2k views

How can I compare the schema of two databases?

Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customers site. We are experiencing problems with crystal reports running some ...
1
vote
0answers
35 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
26 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
46 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 ...
0
votes
0answers
52 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 ...
3
votes
1answer
277 views

Storing created/retired dates for a large amount of different entities

In the db I'm working on now almost every entity has these 4 columns: CreatedDate CreatedBy RetiredDate RetiredBy Usually this is used for logging purposes, and for some of the entities the ...
1
vote
2answers
164 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
433 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
32 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
28 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
4k 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
20 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
18 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 ...
1
vote
2answers
60 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
32 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, ...
34
votes
5answers
80k 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
1k 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
66 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
43 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
65 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
24 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
103 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 ...
0
votes
1answer
61 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 ...