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)

1
vote
1answer
18 views

Should I use TINYINT for ID (PK) field in MySQL?

Should I perform this type of optimisation if I know that a table can't have more records than 10?
0
votes
0answers
10 views

Synchronize more than two databases mysqlyog

I use mysqlyog in order to synchronize two databases. My question is whether I can use it in order to synchronize more databases. (I have one development database and I would like when I make a change ...
0
votes
1answer
50 views

Database design considerations for unused columns with every table has same schema

I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate ...
3
votes
1answer
352 views

PizzaShop - DB - Normalized Schema

I am trying to make a simple DB in Access for a theoretical pizza shop(the db is for an assignment). What I am trying to do is link one type of pizza to one or more types of toppings. I just wanted a ...
1
vote
1answer
135 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
1answer
45 views

Percona and MariaDB-10.0 ignoring not null on table fields

On the percona-5.6 and MariaDB-10.0 servers, the NOT NULL declared on table field are being ignored. I am able to leave fields declared as NOT NULL empty when inserting data and the servers are not ...
1
vote
0answers
16 views

Modeling Multiaccounts + content interactions [closed]

I'm having trouble to model a scalable database schema with performance in mind. The requirements are wide, as such that I need a schema that supports; user can swith between multiple types of ...
0
votes
2answers
41 views

MySQL: Indexing by column which is also a part of a compound primary keys and

I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example: CREATE TABLE `user_visit_stats` ( ...
1
vote
0answers
86 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 ...
3
votes
7answers
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 ...
3
votes
2answers
17k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...
0
votes
0answers
33 views

Help on Database Table Design

I am trying to create a website with multiple Q&A. The main features I need to support are as below There will be multiple types of questions. (a. Single option Answers, b.MultiOption, c. True ...
3
votes
1answer
171 views

Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him. I want to insure that the host you assign to the domain belongs ...
0
votes
0answers
21 views

Can unpredictable feasibility of schema downgrades be handled better?

I have a system backed by a MySQL database, installed via RPM on RHEL-compatible systems. The RPM will automatically run a sequence of "upgrade deltas" (.sql files) to migrate the user's current ...
15
votes
4answers
52k views

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the ...
0
votes
0answers
17 views

Is Schema Level Replication possible in PostgreSQL?

I am very new to the world of databases and wanted to know how one would/could accomplish Schema level replication in PostgreSQL. I've read about Slony, but it does not appear to state anything about ...
1
vote
1answer
184 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
2answers
4k 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 ...
9
votes
2answers
276 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 is: CREATE TABLE sequences ( id ...
3
votes
2answers
68 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 ...
3
votes
1answer
139 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
303 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 ...
4
votes
2answers
1k views

SQL: transfer database schema

If i want to transfer schema ownership to another schema i know that i need to use something like: ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname; But how is it different from: ...
0
votes
1answer
71 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....
9
votes
4answers
2k views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
0
votes
1answer
40 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 ...
7
votes
2answers
146 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 ...
3
votes
1answer
355 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 ...
0
votes
1answer
59 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 ...
0
votes
0answers
60 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
24 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
25 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
32 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
38 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 ...
1
vote
2answers
127 views

MySQL Junction Table as Foreign Key?

I have several tables: company, customer, company_has_customer, and transaction. company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK. In almost all ...
0
votes
1answer
53 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
77 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 ...
15
votes
3answers
7k views

Schema design - best practices?

I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
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
1answer
54 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
0answers
22 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?
1
vote
1answer
933 views

Implementing a one to zero or one relationship

Suposse I am using a SQL platform and want to implement a relationship of cardinality one-to-zero-or-one (1-0..1). This situation would pertain to a business context in which let us say that: A ...
1
vote
1answer
85 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 ...
0
votes
0answers
32 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
63 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 ...
8
votes
2answers
7k views

What is the usage of performance_schema in MySQL 5.5?

I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list. What is the usage of this new schema..? How can we monitor using this schema..? What are the ...
0
votes
2answers
70 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 ...
1
vote
3answers
185 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
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
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 ...