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
14 views

Is my shopping list database schema good enough?

So Im wondering if my schema is correct? It's basically suppose to hold shopping list items for each user, a user belongs to a household. Items will be added to the household. The stocklist is ...
4
votes
3answers
157 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
10 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 ...
5
votes
1answer
51 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 ...
4
votes
0answers
25 views

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

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 ...
0
votes
1answer
58 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 ...
1
vote
0answers
24 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 ...
2
votes
1answer
67 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 ...
3
votes
1answer
44 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
2answers
132 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
1answer
300 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 ...
0
votes
1answer
28 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
188 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 ...
2
votes
2answers
203 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
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
1answer
21 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?
3
votes
3answers
65 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
1answer
143 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
0answers
30 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
21 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 ...
1
vote
1answer
92 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 ...
3
votes
1answer
50 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]; ...
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
322 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
32 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
175 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
108 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
23 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 ...
6
votes
1answer
77 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
41 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 ...
2
votes
2answers
945 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 ...
3
votes
1answer
73 views

Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get ...
7
votes
1answer
103 views

Purpose of owning a schema in SQL Server

I've been spending the last few days cleaning up database permissions, grouping them all into AD groups and removing orphaned users. This has lead me to a recurring scenario: Find a user that owns ...
1
vote
1answer
189 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, ...
1
vote
1answer
43 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. ...
5
votes
2answers
3k views

Query across multiple schemas, unknown until execution time

I have multiple schemas that I need to query across. The schemas are not known in advance, for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
0
votes
1answer
197 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
1answer
34 views

Apply operation on all schemas

I have an Oracle instance, with several schemas (all starting with the same prefix). PATT_A PATT_B PATT_C ... All of them have the same table, and I'd like to update all of them in one shot (with ...
1
vote
0answers
47 views

SQL Server vs Oracle [closed]

I am transitioning from SQL Server DBA to Oracle DBA. I have minimal experience with Oracle, and I have found that the best way for me to traverse if by correlating terminology and database engine ...
3
votes
1answer
755 views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
1
vote
1answer
60 views

Understanding of schema and tablespaces - recommendation for a later seperation [closed]

DLDR: I would like to get a confirmation of my understanding of tablespaces and schemas (see image) a recommendation how to organize schemas and tablespace to make a later separation easy. ...
1
vote
1answer
738 views

MySQL DB structure with Main category and several nested sub categories

I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, ...
2
votes
2answers
246 views

Is there a best practice for recurring calendar events

I'm not a dba but a php programmer, so that I'm not an SQL pro. Now, I have searched in both programmers and dba stackexchange sites but the answers are too specific or not conclusive. My question is ...
8
votes
1answer
481 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
0
votes
1answer
64 views

Divide a PostgreSQL database into schemas

My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this ...
2
votes
3answers
52 views

What schema for different relation from two tables to another?

There are three tables instructors, meeting_points and bookings. Here are the relation I want: 1 instructor has 0, 1 or n meeting_points 1 booking has 1 meeting_point 1 meeting_point can be linked ...
1
vote
0answers
21 views

Alter schema of Access 2013 database with linked table

I'm trying to change the schema of an Access 2013 table. I want to lengthen some of the text fields. When I change the schema and try to save I get dialog with Operation is not supported for this type ...
0
votes
1answer
110 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 ...
10
votes
4answers
9k 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, ...