Questions tagged [schema]

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).

Filter by
Sorted by
Tagged with
1
vote
1answer
31 views

Two substantially different but similar cases: Are they the same in terms of table cadinality?

Imagine these different cases when designing a database: Case 1: One book (only one instance of a title physically) can be borrowed by many people, but only one person at once but many people during ...
0
votes
1answer
15 views

Is there a way to specify which schema the database task “Export Data-tier Application” exports for the other entities besides the tables?

I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object ...
1
vote
0answers
18 views

Default Schema not being applied even after setting default schema and default DB

Hello Stack Exchange! I have an interesting problem! Let me set the stage for you: 1) Windows Authentication 2) Default Schema identityiq has been set for the user with login XCORP\USERA 3) ...
0
votes
0answers
28 views

Is it possible to have SQL server look for entities under other schemas than DBO when the schema name isn't specified in the query? [duplicate]

Not sure if this is a thing, and I understand it's better practice to write your queries using the two-part naming convention, but it would be cool if SQL Server had a way to setup a schema priority ...
1
vote
0answers
25 views

CMS - Database Schema Review / Questions [closed]

Database Schema Review for CMS I'm in the midst of creating a Laravel application that will (hopefully) rival WordPress. The thought process is quite similar. Developers are able to create resources (...
0
votes
0answers
22 views

ER Diagram: A relation is dependent on another relation

Take a look at the ER diagram here: ERD. This is a fragment of the whole ERD. I've included only the part relevant to this question. This somewhat hypothetical database has 4 entities. Course, ...
0
votes
1answer
27 views

Query data that does not fulfill constraints in table in Oracle DB

In Oracle I have a lot of tables in a given schema. Some of the table constraints in the schema have been disabled. I have to know for all tables in a given schema with any of the constraints ...
0
votes
1answer
18 views

How to create multiple azure sql database in an elastic pool using powershell script?

I want to create a sql server and deploy the elastic pool on that server. Then create multiple azure sql database in that elastic pool using powershell script.
0
votes
0answers
26 views

Should I use two indexes? [duplicate]

I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables: Private Index (uses int), for relational table purpose only ...
1
vote
2answers
75 views

Possibly inconsistent database?

Suppose I have a database schema with 3 tables - Area, Work and Author. Area is a table which contains the list of Areas of interest of an Author / Areas in which a Work has been published, for ...
1
vote
0answers
28 views

Schema design for an entity with 'heterogeneous' status types

Consider a notification system which gets some event data and list of business email addresses as input (the list is basically computed by looking at which users/addresses are subscribed to which type ...
1
vote
1answer
67 views

Database schema initially not designed for scaling, how to optimize it?

I developped 2 years ago a back office for helping a logistic company to manage the deliveries. At this time, the company only had one "partner" (let's call it Partner A). The main table is ...
-2
votes
1answer
40 views

Differentiate data between test and prod data in sql server [closed]

we have a production database in ms sql server and my team is also testing on that same prod database. I want to know which are test data so in future I can delete these entries. How do I separate ...
2
votes
1answer
418 views

How to force a sysadmin to store data in schema other than dbo? [duplicate]

I am using a GUI tool to upload data in my SQL Server 2016 database. The user being used for above task has been granted sysadmin permission I created a dedicated schema to store above data. This ...
0
votes
0answers
19 views

Schema design for infinitely expanding variables

I'm putting together a schema for a progressive story that will track user choices. Because it will unfold over at least a year, I need a schema that is safe to handle constant additions. I have a ...
0
votes
2answers
51 views

Database design for job board based on neighbouring cities [closed]

I'm trying to implement the relationships for the data represented below. I have a microjob board, users sees job listings in their city and neighbouring cities user 1 lives in city1 user 2 lives in ...
2
votes
1answer
27 views

User and Schema Recreated during Stored Procedure Creation

As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually ...
2
votes
1answer
29 views

Is there a way to watch for schema changes in mySQL?

ALL, Is there a way to watch for a schema changes in mySQL? Or I will have to continuously query information_schema tabes? TIA!!
2
votes
0answers
91 views

PostgreSQL Schema Builder/Diagrams [closed]

I have worked with PostgreSQL, primarily using the Django ORM (started with MySQL and migrated to PostgreSQL) and using pgAdmin for maintenance tasks. I am now working on an open source project ...
0
votes
0answers
39 views

What's the best way to model similar tables with overlapping functionality?

I'm building a sports event/tournament/league management app for myself, and I'm stuck on the best way to model how Teams should relate to the different competition types. Some background regarding ...
0
votes
3answers
53 views

How to set AUTHORIZATION on CREATE SCHEMA

I'm looking to create a new schema for an existing database, which currently only uses the dbo schema. I'm after advice on how to set the AUTHORIZATION (owner name) and what affect this will have. 1....
-1
votes
1answer
23 views

Is there a best practice design for handling different types of the same object which have different properties?

This may be a rather naive question and I am not even sure if I am asking it in the best way, so my apologies if not. I would like to know if there is a standard, or best way to handle cases where ...
2
votes
1answer
109 views

Why can't I create a new schema with the owner set to dbo?

I created a test account for checking permissions against. It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database. But when it tries to create a new schema ...
0
votes
0answers
25 views

SQLServer: Storing File Extension and normalization?

After much research, I've decided to store pdf/img/txt/doc/etc files on a web server instead of a database. However, the reference to that file will be stored in SQL Server. So far I have... | ...
0
votes
0answers
7 views

Guidance on my NoSQL Schema

I'm working on an application in which I'm in need of a database. I chose NoSQL (DynamoDB) for the app. My Unique ID is set to a user sub but its payload is a list of objects. There are 191 of these ...
0
votes
0answers
27 views

Microservice - Catalog and inventory tracking on different tables

I'm currently exploring the microservice architecture and base on what I've read so far it's good to separate the catalog microservice/database to inventory and that's where I want to go. The ...
0
votes
0answers
21 views

Designing Doctor-Pacient access permissions

Id like to have several admins which in this case are "doctors" which will have access to their patients, however, a patient might have more than one doctor who has access to their data. What is the ...
1
vote
1answer
31 views

composite index on bigints

I have a User Relations table, which basically stores all the one-directional relations of users. For example , User A follows user B ; User X blocks User Y User ids: the id of users are bigints. It'...
0
votes
0answers
22 views

Strategies/Schema for Unique Constraint depending on Grandparent ID

I'm using Rails 4 with Postgres (newest version). Wondering about design theory and best practice. Here is my current schema: create table Company ( id int not null primary key ) create table ...
0
votes
0answers
26 views

Dynamic script to alter a column type having an index

I am writing a script to update the datatype of a column in SQL Server on a list of tables, if the existing column are not yet of the desired type. The column should be converted from a VARCHAR to a ...
1
vote
0answers
79 views

Schema for social network using the supertype/subtype pattern

Scenario I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
0
votes
0answers
66 views

Instant Messaging Schema design advice

I'm trying to build an Instant Messaging functionality in my app as part a bigger project. Chats can have more than 2 participants (group chats) If participant A delete a message, it still should be ...
3
votes
1answer
35 views

Setting up relationships on a gaming schema with multiple locales

I've devised the following schema: Where BotLocale holds locales like en-US, es-AR, etc. GameArtifact has a primary key which is artifact_id and GameClass with locale, identifier being the primary ...
1
vote
2answers
27 views

Generate table of all databases and schema with a single psql command

In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command? To be concrete, let me provide an example. I have two databases in a PotgreSQL database ...
1
vote
1answer
82 views

Example where schema are better than multiple databases

In several database systems (e.g., PostgreSQL and MS SQL Server) there is a concept of schema. There are many questions out there asking what schema are and how they are used; here is a good one. ...
0
votes
1answer
25 views

BigQuery - load data with changing schema [closed]

I have situation when an external system is providing data to BigQuery instance as JSON files. They are rows, that have been updated in that external system, I need this data in BQ for later ...
0
votes
1answer
60 views

Set up read-only privileges on the dbo schema, but allow creation of entities in another schema?

Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for ...
3
votes
1answer
125 views

What is the purpose of “Schema Owner”?

If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema? Does my new schema inherit the same permissions from it'...
2
votes
1answer
44 views

ERD - Multiple FKs with only one of them being filled for each record

I'm designing a database schema for a music platform. Some of the entities as follows: track album band artist A "track" can have an "artist". It can also have a "band" which makes the artist ...
1
vote
0answers
32 views

Schema design: Anonymous user's data across registration

This is a schema design question; implementation is going to be Mongo, but answers are platform agnostic. Given an app, which has users, sessions, and items. All visitors will have a session ...
1
vote
1answer
47 views

Table design - parent with always exactly 1 or 2 children

Lets say I have the following existing tables (both with a primary key column named Id): TableA TableB Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 ...
0
votes
2answers
390 views

Specifying Order of Executing Stored Procedures

Issue: The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error: Msg 2627, Level 14, State 1, Procedure ...
1
vote
0answers
48 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can ...
0
votes
3answers
241 views

Create Synonym error for a user-SQL Server

I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission. When the user tried to create synonym as shown below. USE [DEMODB] GO ...
0
votes
2answers
25 views

Database layout to keep track of the “freshness” of a column value in a specific row

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns? For instance, "for the Person Y, the name has changed 2 days ago, and ...
-2
votes
1answer
28 views

Requesting review of proposed database for Project Billing [closed]

I've had enough experience with databases to get me this far, but I was hoping someone could validate my design. Once I have this confirmed, next steps would be to implement in some tool, and use ...
1
vote
2answers
39 views

Does an Oracle user have all privileges in its schema?

I'm learning Oracle and did some testing. I used the sys user and created a table t in the scott schema and I found t's owner is scott, and scott has full access to t. I want to confirm, does scott ...
0
votes
0answers
30 views

One table to hold all ids related to one user - bad idea?

I was wandering if it's a very bad idea to have one table that holds all the ids related to one entity? For example let's say i have 3 tables each containing data about one user. Should i use an ...
0
votes
1answer
72 views

Storing dates and their history of changes and emailing - Does my database design pose any problems?

Background I'm designing some tables to allow dates to be assigned to an object so progress can be tracked on that object, along with functionality to store emails sent pertaining to these contracts ...
0
votes
1answer
45 views

is “create schema” the same way of saying “create database”?

I'm a beginner in DBMS, sorry if my question sounds dumb. I often see some textbooks says: create a schema . does it that mean "create a database"? But isn't that a schema is also a skeleton of a ...

1 2 3 4 5 17