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

3
votes
1answer
79 views
+50

User Privacy Settings DB Schema Design

On websites, like Facebook and Google+, you find settings (a.k.a. Privacy settings) which allow you to decide who is allowed to see what you share. How are such settings structured, or what is the ...
1
vote
1answer
130 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 ...
0
votes
1answer
415 views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
3
votes
1answer
55 views

Relational or NoSQL Database Design

Disclaimer: I also posted this question on Stack Programmers, because I was not sure which was the most appropriate. This database holds assembly instructions data for shipbuilding, later use in a ...
2
votes
0answers
14 views

How do I quickly tell the structural differences between two databases? [duplicate]

I am upgrading a web application to a newer version and need to migrate the data manually. The newer version uses slightly different database structure. It has added a few tables, but all of the old ...
0
votes
1answer
23 views

Is it good to save Keywords stored by users in a new DB schema?

My question is more from the schema design and of further performance of the DB. I'm designing a DB schema which will store primarily strings. Actually, users work on these string, as normal mortals, ...
2
votes
0answers
27 views

Getting error 1064 in automatically-generated code from MySQL workbench [duplicate]

I have started using mySQL workbench (Version 6.3.4.0) and I have used the EER Diagram tool to design a schema. So far, so good. However, when I try and run the Forward Engineering, I get the error ...
1
vote
1answer
20 views

Is this the correct way to model a relationship between an object which may or may not have another object?

I'm creating a database for a department store and the database has two tables: Items and Coupons. An item, like a TV, can only ever have one coupon. Some items don't have a coupon at a given time. ...
0
votes
2answers
144 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 ...
0
votes
0answers
32 views

How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres

I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions. But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i ...
1
vote
1answer
487 views

Easier way to change every user in every database to default schema = dbo?

There was recently a domain migration where our SQL Server is hosted. I have two servers and about 30 DBs on each. Every user was changed to the new domain but in doing so, their default schema ...
1
vote
1answer
51 views

SQL Server 2008 R2 copy Schema with all user security permissions

I have a prod support request from a high value user to adjust the settings on a company-wide dashboard so that he can manage a section of his workflow currently being excluded from these reports. ...
1
vote
1answer
133 views

Database Schema for Resume/CV and Separate Sections

I am trying to figure out if I am on the right track here. resumes id user_id resume_sections id name desc Sections are those that we normally find in resumes, experience, skills, education and ...
0
votes
2answers
259 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 ...
0
votes
2answers
2k views

Oracle schema import is not importing all the tables present in the schema dump file

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not ...
0
votes
2answers
58 views

Best Approach to Migrating Oracle to MySQL - Run on EC2 in AWS

I'm trying to migrate an Oracle schema as well as all of the data within the DB to a MySQL database. I want to then make an EC2 app on AWS to kick off whatever I've written for this. Are there any ...
2
votes
1answer
123 views

Building a restaurant database

I am designing an Italian restaurant database, but I face some problems considering the database design, which lead me to a halt. The part that I find difficult developing is the dishes table. A main ...
0
votes
1answer
1k 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 ...
3
votes
1answer
132 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
0answers
14 views

Schema for ACL/User/Group/Role management

I'm building an app with some fairly complex permission and ACL rules and want to get my schema right. These are my goals: A user signs up on behalf on an organization. Organization has groups. ...
2
votes
2answers
206 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 ...
1
vote
1answer
59 views

Is my shopping list database schema good enough?

I'm wondering if my schema is correct? It's intended to hold shopping list items for each user, a user belongs to a household. Items will be added to the household. The stocklist will to hold the ...
0
votes
1answer
42 views

Copying MySQL schema, some tables not getting recognized!

From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are ...
8
votes
2answers
189 views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
2
votes
1answer
205 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, ...
0
votes
1answer
33 views

Create postgres schema in db with multi user in AUTHORIZATION

Is it possible to create a schema that is owned or accessible by more than one user in the same database? I was reading postgres create schema documentation and postgres alter schema documentation ...
9
votes
2answers
117 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
1
vote
1answer
93 views

Database schema for entities with two possible owner/parent types?

I'm using PostgreSQL with Sequelize as my ORM. I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
2
votes
4answers
1k views

How to find out what caused a schema change?

I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on ...
2
votes
2answers
1k views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
5
votes
3answers
220 views

MySql Invoice and Subscription Schema

I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below. orders id, customer_id order_items id, order_id, product_id invoices id, order_id subscriptions id, ...
0
votes
0answers
12 views

Is it possible to move more than one database object into a schema using the `ALTER SCHEMA` statement in SQL Server 2008 R2? [duplicate]

I have a TSQL Statement like this: ALTER SCHEMA FindUsedAndUnused TRANSFER dbo.UsedUnusedNotSure_the_counts; And when I try to run it with multiple TABLEs or VIEWs I receive an error; Is there ...
4
votes
0answers
30 views

Should I use a separate postgres schema for application logins? [closed]

I'm building an application. I have users, roles, and permissions; users can have one role, and a role can have multiple permissions. As such, I'll have a Users table, a Roles table, a Permissions ...
1
vote
0answers
35 views

Allow user select from table in another user's schema that has policies set

I have an oracle10g database with 2 users - user A and user B. I want to select data from a table in user A's schema from user B. I have granted user B select privileges on the table in question but ...
3
votes
1answer
50 views

Schema for scarce goods

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I ...
0
votes
1answer
34 views

Can SQL Server 2014 schema be defined using ODBC?

I am trying to use the ODBC driver for SQL Server 2014 to export data from an Autodesk Revit 2015 model to a database. However, I would like to export to a default database (e.g. db1), creating a new ...
1
vote
2answers
205 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
0
votes
1answer
42 views

Installing OE (Order Entry) schema in Oracle Express

I'd like to know if it's possible to install OE (Order Entry) schema in Oracle Express. If so, how can I do that?
2
votes
3answers
71 views

Schema for a lease platform

I have following tables: A room I want to lease (has user.id as ForeignKey) A users table for users who can rent it An availability table for time periods the room is to use with prices for that ...
0
votes
0answers
33 views

Where to put the currency field?

Abbreviated I have following tables: Companies own flats and rent them for specific periods_in_time and lastly a currencies table. The price column is sitting in the periods_in_time table. But where ...
0
votes
0answers
26 views

Multiple table relationship for different scenarios!

We have the following heirarchy in our platform; Package is comprised of Products, Products is comprised of Services and Services are comprised of SKUs. The relationship between them is many-to-many ...
3
votes
1answer
61 views

How to create a login and user that can only access one schema in one database

I have executed the following: USE [MyDatabase]; GO CREATE LOGIN [MyDomain\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDatabase]; CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]; ...
3
votes
1answer
331 views

Best embed/reference/field strategy for many inserts (mongo)

I'm building a gaming backend with Mongo, and have some issues on how to best design the schema to maximize performance and database size. My models: User Match -ReferenceMany (User) ...
0
votes
1answer
49 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
5
votes
1answer
191 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 ...
0
votes
1answer
31 views

Unexpected SELECT permissions

My user WebApp seems to have SELECT permissions on every table in every schema and I can't figure out why. WebApp is only in the Public role. Public doesn't own any schema. Explicit permissions ...
2
votes
1answer
370 views

ERROR: no schema has been selected to create in

I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not ...
0
votes
1answer
24 views

Do i need “CHECK CONSTRAINT” when i am using “WITH CHECK ADD CONSTRAINT”

I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows: ALTER TABLE [dbo].[ConsoleServer] WITH ...
7
votes
1answer
104 views

SSDT Schema Compare fails when comparing my DB Project to my Azure Server

I have a SQL Database project that I have built our enterprise DB on. It has deployed several times on internal and AWS hosted SQL servers using SSDT's Schema Compare tool. The problem when I post ...
2
votes
1answer
44 views

Database Table Design

I would like to ask couple of questions regarding my schema table design. I have the following data coming from an external database where I do not have any control over it. PROD_NO PROD_NAME ...