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)

3
votes
2answers
50 views

Efficient schema to handle time interval queries on a huge table?

We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table ...
0
votes
1answer
57 views

pt-online-schema-change — can't get it to stop quitting w/ “Threads_running=108 exceeds its critical threshold 100”

I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this: pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX....
7
votes
2answers
142 views

What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this ...
0
votes
0answers
35 views

Design schema for booking appointment/time slots

I'm trying to figure out the best way to design the database. I already have a Client table which consists of id, name and email. A client can book many dates for appointment and each date has only 3 ...
1
vote
1answer
23 views

Insert column name as value

Consider the following, in PostgreSQL: drop table test ; create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ; insert into test values ('red',1,2,3) ; I ...
1
vote
0answers
18 views

Database Designs Alternative to Polymorphism in Establishing Relationships

I'm working on a database which handles replacement of parts in a machine. Rules A machine has many components A machine has a set of assembly instructions Many components have many part (screws, ...
0
votes
0answers
26 views

Tag system for multiple Item types and groups Schema

I have a groups table and users table, and a many to many relationship between them. In my app, there are different modules, like question, product,suggestion , etc. Users can create products/...
1
vote
0answers
26 views

pub/sub database schema

I am having a hard time creating a pub/sub database schema with stateful metrics. Saying it aloud it sounds easy enough: "There are many organizations that will subscribe to checklists that are ...
0
votes
1answer
37 views

Oracle 12c : Unable to install sample schemata on Windows 10

I've gone through lots of trial and errors in installing the sample schemata for Oracle 12c and still can't get it right. I have a pluggable database named PDB01. I also have an instantclient ...
0
votes
1answer
75 views

Products with different prices in an order

I have a "classic" database schema for order and products: Orders ------ id etc. Products -------- id name etc. Order Products --> Pivot table -------------- id --> some people don't like ...
3
votes
1answer
115 views

How to prevent SSDT publishing from dropping columns

I want to create a publishing profile that DOES a complete schema comparison and publishing BUT does not drop any tables or any columns that have been removed between the old and new version. I am ...
0
votes
1answer
21 views

Is there any database system that allows to have different schemas for different users but the same content for all users?

In my fantasies, it looks something like this: User 1: SHOW TABLES; +-----------------------+ | Tables_in_my_database | +-----------------------+ | table_1 | | table_2 | +...
0
votes
0answers
20 views

Cannot drop schema sql server

I'm trying to delete a schema but I am with the following message: Lock request time out period exceeded. (Microsoft SQL Server, Erro: 1222) Does anyone know how I can fix?
0
votes
1answer
51 views

Problems with queries [closed]

I have problems with queries in MySQL. Problem 1: Query: SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA="mysql" and TABLE_NAME="servers" MySQL says: #1054 - Unknown column 'mysql' ...
0
votes
1answer
39 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both need to ...
1
vote
1answer
50 views

Importing .dmp file into SQL developer: Do I need to import seperately for each table?

I have a large .dmp file which I would like to import into SQL developer. This contains all the data I want to import across several Schemas. Do I need to split this up into each separate table in ...
1
vote
1answer
89 views

Slot Time Challenge - Doctor Appointment database schema

I am trying to make a doctor appointment system where patient online will have two options: First time visit (this visit should be 20 mins) Follow up visit (this visit should be 10 mins) ...
0
votes
0answers
29 views

Mongodb database design

I'm planning to implement this schema in Mongodb, I have been doing some readings about schema design, and the notion was whenever you structure your data like a relational database you must be doing ...
0
votes
1answer
59 views

Maximum number of TEXT/BLOB columns per table/performance of table with many of them?

I've created a system that generically imports CSVs into a database table with metadata columns for keeping track of when a CSV record has been changed, and it allows filtering on CSV columns instead ...
0
votes
1answer
13 views

What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?

I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
0
votes
2answers
58 views

Migrate SQL server data to new schema

I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access. The original 'developer' was a non-geek who had been sent ...
0
votes
0answers
5 views

How would I script standing up a MySQL instance with certain specs?

Context: I seem to be unable to find in MySQL Workbench schema creation process where I can adjust server specs like max memory (2 GB); CPU usage, 1x 4 core; 20 GB SSD/Flash storage; OLTP; OS (RHEL ...
1
vote
1answer
16 views

Joining on a M-N lookup table vs keyword searching a single column

I am in the process of writing an image gallery webapp similar to that of Danbooru, and in the process, decided to take a look at danbooru's database schema for an idea about how things are structured....
0
votes
1answer
41 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. (rsErrorExecutingCommand)...
0
votes
0answers
26 views

Cassandra filtering multiple items

I am attempting to learn Cassandra by taking my RDBMS database and create a nosql version of it. Forgive any mistakes I am just making a quick example so I can ask my question. Here is my example(...
0
votes
2answers
26 views

Good reason to convert LONGTEXT column to smaller width if field is smaller?

We have a database that uses a LONGTEXT type for a column that does not need that much space. The maximum row size currently has about 6500 characters and that's with a few million inserts so it's ...
3
votes
1answer
36 views

Store the content book in database, so that it would be updated afterwards or store them as document?

I have some books in (urdu) made with Urdu Inpage(tool for making pages in urdu) like this: problem that these document files are difficult to read in php because they gives unread-able characters, ...
2
votes
1answer
43 views

Permissions on Trigger Problem

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem. The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the ...
1
vote
1answer
37 views

Design db schema in correct way for parent child kind of requirement

I am creating schema of tables. But not able to understand best way to do it. Following are the tables: Car Model - It contains all car models with types. For eg. Mercedes(brand) - GLA 250(...
1
vote
3answers
136 views

Difference between a schema and a database in db2

I can not figure out what is the difference between schema and database in db2 I find this SQL so confusing for example : CREATE TABLE schema1.table1 .... .... .... IN database1.tabspace1 Can ...
0
votes
1answer
50 views

PUBLIC privilege potential security threat - Oracle database

Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only ...
1
vote
1answer
28 views

Is it possible to create a multi-layer schema in SQL Server 2008 R2?

For instance so you can specify a department, and then maybe have finer levels of granularity? CREATE SCHEMA hr; CREATE SCHEMA hr.reportl; CREATE SCHEMA hr.report2; The last two statements fail.
1
vote
0answers
42 views

pg_restore slow after adding a check function

I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records. The way I did this was to remove the foreign key on some tables in ...
1
vote
1answer
46 views

Relationships mess

Say I have a table ads, and each ad has several medias - held in a medias table. Each media can be one of several types. Types are in a media_types table. So, for example, an ad has two medias: one ...
0
votes
1answer
105 views

ORA-00942: table or view does not exist- Can it be solved by granting privileges to user?

I am creating a Java application that connects to an Oracle Database. In the application I connect to the DB as User A, which works as intended. I then try to access tables in Schema 1 and Schema 2....
3
votes
2answers
115 views

How does a users DEFAULT_SCHEMA get set to something other than “dbo”

One of the settings for a SQL Server user is the DEFAULT_SCHEMA. This is frequently dbo but not always. I realize you can change the setting by using this code: ALTER USER [UserName] WITH ...
0
votes
0answers
41 views

Registration Schema Implementation

I'm building an online app that is going to handle reservations. Individuals making reservations can make one for a single day or multiple days. I have come up with two schema and cannot see much of a ...
0
votes
0answers
23 views

Address database scheme - multiple links

I am currently building a databse scheme for a small company. But at the point doing it I figured out that my attept will be unacceptable due to the ability that one action will affect multiple ...
1
vote
0answers
38 views

Handling Strong and Weak entity types in a one-to-one relationship

I am modelling a database for a term project and I have come across something I do not have a definite answer to. Diagram I was supplied with the following Entity-Relationship diagram: ...
5
votes
2answers
474 views

Should we create a schema per “project”?

This is a database where users create a 'project' and work on it. We have a table for project and various other tables that contain different attributes for project (multiple rows for each project ...
0
votes
1answer
61 views

Does exist some software for transforming data from one DB schema to another?

I need to transform between two different database schemas the same data. For example i have one table in old schema like this: and i need to transform it to new schema like this: So i have few ...
0
votes
1answer
56 views

What is the name of the default schema in Oracle?

When you create an object in Oracle, and do not specify a schema (object prefix), is there a name for that default schema? I see in Oracle SQL Developer, that a newly created object shows up in the ...
0
votes
1answer
25 views

Database schema for timesheet app?

I am very new to database managements as I am enrolled in my first class involving databases at university. For my project, I have to create an app that clocks employees in an out and keeps track of ...
0
votes
0answers
17 views

Category of the same object in SQL and Inheritance.

I am trying to make the relational schema for a shop which sells ice creams and drinks. As the ice creams and drink both fall in the same category of Items, i tried making the items entity but i am ...
0
votes
0answers
22 views

Table design one or more

I am struggeling with the following issue. I have a table executives and supervisor, quite similar in the structure. Both of the executives can have a Car one or more. Should I make a table ...
0
votes
0answers
13 views

Database ERD for orders info

I have two tables: 1-orders order_id 2-shipping_and_billing order_id FK address_type(billing and shipping ) city I need to get full info of shipping and billing in single select of ...
3
votes
0answers
88 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color VARCHAR(...
1
vote
0answers
66 views

Database design in MySQL workbench

After doing some change As Mr Vérace told me here in this question, by changing the id names of each table into table_name_id and using the singular notation, I still need your opinion about this ...
1
vote
0answers
39 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender ...
1
vote
1answer
150 views

Database design for dentists

I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists. Here is a picture of the database design: ...