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)

7
votes
1answer
214 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 ...
4
votes
1answer
179 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 ...
2
votes
3answers
136 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
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 ...
2
votes
1answer
80 views

Database templates [duplicate]

Are there any site with database templates for specific purposes? (i.e. a database schema for users and authentication in a webpage) for different databases like Oracle, MySQL... I have been looking ...
8
votes
1answer
661 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 ...
0
votes
0answers
109 views

pt-online-schema-change with statement based replication in MYSQL

Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR). What is the better way of doing it as currently I have ...
1
vote
1answer
253 views

Referral table structure

I need to make a simple referral system and I have a database table set out as follows: mem_id | date | refer_email | accepted And the meaning of such columns is the following: mem_id: The ...
0
votes
1answer
95 views

Database design: handling time

I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to ...
3
votes
0answers
47 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
0
votes
2answers
219 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 ...
1
vote
1answer
236 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
2answers
312 views

MySQL JOIN two tables and get latest result

I want to JOIN two tables and get the latest result from each one of those two in a single table. I'm kind of concerned about speed too since tables are growing kinda fast. Close to 60-70k records a ...
3
votes
1answer
351 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 ...
0
votes
1answer
68 views

Generate Schema Only Script from SQL Server 2012 to SQL Server 2008R2

Hi this is the first time that I've encounter this error in generating script for my database, what do you think causes this error? Thanks. Please see image.
0
votes
1answer
71 views

Does MariaDB support schema changes inside a transaction?

MySQL doesn't support schema changes, inside a transaction so there's no way to roll back after during a failed migration involving schema changes. This capability is supported by Postgres and SQLite....
3
votes
2answers
4k views

Error 42P01: relation does not exist

I'm new at creating databases, and this error has me dumbfounded, as I am super new with DB admin things (I mostly do reporting type queries). I created a new database through pgAdmin3 GUI, and I'm ...
0
votes
0answers
56 views

Is doing an online alter on a MySQL table to add comments safe?

Is doing an online alter on a massive MySQL table to add or edit table or column 'comments' safe? Or do you need to take precautions like you would while adding, removing columns, etc on that table?
0
votes
1answer
198 views

How could I improve my DB design?

I'm a second year software engineering major looking to create an iOS app that connects to a MySQL database (database not yet created) over the summer. I have a strong understanding of programming and ...
2
votes
0answers
103 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
0
votes
3answers
123 views

Schema Design with “Seasonal” Data

I am developing a SaaS web application for School Management. What I need help is this: The whole application is purposed for the current year that the School is in. However, some data and ...
0
votes
1answer
149 views

How can I limit a user to only be able to run procs in a schema, which access another db?

In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything ...
0
votes
1answer
63 views

Normilsation - 2NF and 3NF

I've been through several questions and youtube tutorials, i understand that 2NF is removing the partial dependencies and 3NF is the transitive ones, but i can't get my head around how the following ...
1
vote
1answer
66 views

How can I create a MongoDB based on MySQL schema?

How can I create a MongoDB based on MySQL schema? This is my schema: productCode VARCHAR(15) productName VARCHAR(70) productLine VARCHAR(50) productScale VARCHAR(10) productVendor VARCHAR(50) ...
2
votes
1answer
276 views

Using the output from mysqldiff.exe to transform database2 to match database1

This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line. I have recently been trying to use mysqldiff.exe, part of MySQL ...
2
votes
2answers
257 views

Schema to make a Payment History/Viewing History

On some websites I have seen two history of events: Payment History and View/Watched History. I want to have both payment and viewing histories in my app, but I got stuck when I started thinking ...
1
vote
1answer
240 views

db schema for a object with multiple foreign key tables

I have a question table which contains question_id, question_text and choice_type_id ( say freetext, binary choice and multiple choices ) Now, the question table (which has question_id, ...
1
vote
0answers
23 views

How to differentiate between class and attribute

I have this problem: Library has books, VDOs, CDs Journals. It gives loan to users. Books and Journals can be referential which cannot be loaned out more than 2hrs and non-referential which can be ...
2
votes
0answers
83 views

Tuple Relational Calculus help? [closed]

I have the following three questions in relation to the following diagram: 1) How many copies of the book titled “The Lost Tribe” are owned by the library branch whose name is "Sharpstown"? {b....
2
votes
1answer
52 views

Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column?

In similar spirit to How to implement a 'default' flag that can only be set on a single row I would like to specify that, for any given list of possible names for a business, only one of the ...
0
votes
1answer
887 views

Is an ER diagram an example of a conceptual, logical or physical schema?

I have been reading databases, and I clearly understand the difference between a conceptual, logical and physical schema. An ER diagram is an example of which of these schema's?
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 ...
1
vote
1answer
534 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 ...
6
votes
1answer
2k 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
2answers
223 views

Is EAV an appropriate design for a collection of coexisting metadata-rich “fact estimates” on objects? [closed]

I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For ...
1
vote
0answers
30 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
0
votes
1answer
1k views

Select rows from table where each row refers to a different schema

I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by ...
0
votes
0answers
841 views

Railway Reservation System [Miniature] Schema

I am working on designing a schema to support the development of a miniature railway reservation system, using oracle 11g XE. This is for a project purpose, I am need to be sure about the correctness ...
1
vote
1answer
877 views

Use a set database schema or EAV design? [duplicate]

I have already created a database design with a set schema. The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders ...
1
vote
0answers
56 views

Mongodb schema design validation

I am building an app which allows users to do some "tasks" to earn points. The "tasks" are part of a "campaign" which themselves are a part of "brands". I am confused about the schema for storing the ...
3
votes
1answer
529 views

How to efficiently create a new PRIMARY KEY CLUSTERED definition online

Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online. I would ...
0
votes
2answers
29 views

Can we determine FDs if we are given some tuples?

I am taking a databases course and i'm learning about functional dependencies. I have searched around and did not find any information on this. My questions is if we are given a table with some tuples,...
2
votes
2answers
743 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 ...
0
votes
2answers
48 views

Is it a good idea to add a reserved field to a large MySQL table?

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible. Is it a good idea to add a text column to each table (say, meta) to save some ...
0
votes
1answer
268 views

Dropped connections, corrupted schema … is MySQL sick?

Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are: Not being able to forward engineer said ...
0
votes
0answers
25 views

How to model role and different activities

I am new in the database design and I'm facing this problem, I hope somebody can help me. In my domain we have 3 roles, Non-Supervisor, Supervisor, Manager. I created a Person table and a role table ...
4
votes
1answer
14k views

SQL Server: grant select access to a user in a view and not in its tables

I have a SQL Server 2012 instance with a few databases. In one of them I created a view, that selects tables in more than a database. I want a user to be able to select that view, but it must not ...
0
votes
0answers
78 views

what is better, multiple column or multiple rows while schema design

I would like to know, which is best in fast retrieval and reporting as numbers of records will increase with time. So lower the record better would be the performance. Priority would be fast response ...
0
votes
1answer
456 views

How does Amazon create the RDS for Postgres? Some kind of Docker?

I am really curious about the infrastructure of Amazon. When I create a new instance of RDS, it gives me a host, username, password.. Everything. Behind the curtains, how do they do this? It´s some ...
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 ...