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
70
votes
5answers
12k views

Is there a name for this database schema of key values?

We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one ...
4
votes
1answer
1k 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 ...
25
votes
3answers
16k views

What are some best practices for using schemas in SQL Server?

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, ...
8
votes
1answer
1k 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 ...
8
votes
2answers
37k 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 ...
68
votes
5answers
11k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
34
votes
7answers
132k 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 ...
22
votes
2answers
2k views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
3
votes
1answer
208 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 ...
1
vote
1answer
575 views

Schema for multi-account database

I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access. I'm putting together the schema for it and I have a few ...
86
votes
8answers
216k views

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? ...
6
votes
1answer
4k views

SQL Server: hierarchy of permissions for schema?

We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is ...
5
votes
1answer
692 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 ...
4
votes
1answer
8k views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
22
votes
6answers
24k views

Is there any good tool for making Database Design and Prototypes? [closed]

I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
30
votes
4answers
29k views

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
6
votes
3answers
2k views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
10
votes
2answers
5k views

Do schema changes “break” Availability Groups or are they handled transparently?

My organization is planning to adopt SQL Server 2012 Availability Groups and I am trying to understand what impact (if any) it will have on our application upgrade process. We release application ...
18
votes
6answers
46k views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two tables?...
29
votes
2answers
169k views

How to transfer data using expdp and impdp commands?

I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have ...
12
votes
2answers
34k views

Allow user to do anything within his own schema but not create or drop the schema itself

I have created a schema in SQL Azure and granted the following permissions to a database role: CREATE ROLE myrole AUTHORIZATION dbo; EXEC sp_addrolemember 'myrole', 'myuser'; CREATE SCHEMA myschema ...
7
votes
3answers
41k views

Database design of Room Booking system in a Hotel

Intro and System Description I am currently designing a Database for a Room Booking system in a Hotel. Client fills a Form / Request with following information about Room: amount of people in room ...
22
votes
17answers
55k 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 customer's site. We are experiencing problems with crystal reports running some ...
15
votes
2answers
2k views

Schema-less/flexible + ACID Database?

I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be ...
5
votes
1answer
2k views

ERD for a web development company

I am working on an ERD which represents the relationships between entities in a Web development company behavioral model. There I have entities like: Customer Service ----------- (like: web ...
4
votes
1answer
2k views

mysql change schema on the fly

Recently I faced the problem that my Db became very huge and I cannot allow to myself downtime every time when I want to change the schema. This caused me to start looking for solution that do schema ...
12
votes
5answers
27k views

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a ...
3
votes
1answer
2k views

How to normalize my db schema for a quiz application? [duplicate]

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 ...
0
votes
1answer
4k views

Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

I'm working on a question/answer project and having a hard time coming up with a suitable database structure. Each question can have from 1 - "n" answers, and each answer is associated with one of ...
17
votes
6answers
4k views

Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project

I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema ...
1
vote
1answer
3k 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: ...
1
vote
1answer
4k views

How To Hide Schema from user

I have DB with 2 schemas: A and B. B schema's procedures use A schema's procedures. I need to create user, which will not be able to see A schema. This user can use only B schema, but i want to let ...
264
votes
4answers
507k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
44
votes
1answer
22k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
11
votes
3answers
36k views

Schema design for products with multiple variants/attributes?

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes. From all the research I've done ...
25
votes
2answers
25k views

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. ...
8
votes
1answer
13k 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 ...
5
votes
2answers
2k views

Schema for Financial Database

My company is starting a new initiative aimed at building a financial database from scratch. We would be using it in these ways: Time series analysis of: a company's financial data (ex: IBM's total ...
11
votes
3answers
48k 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 ...
5
votes
2answers
3k views

Is it possible to get schema ID in another database without using dynamic sql?

I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic ...
2
votes
1answer
217 views

Column type unknown at creation time

I have a table in a MySQL database that stores survey results. CREATE TABLE survey_responses ( response_id INTEGER UNSIGNED AUTO_INCREMENT, survey_id INTEGER UNSIGNED NOT NULL, question_id ...
15
votes
1answer
37k views

How to transfer the ownership of the dbo schema in SQL Server 2012

I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot: Transfer ownership to another user Drop the user from DB (though I can ...
10
votes
3answers
1k 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 ...
6
votes
2answers
2k views

How many fields is too much for a primary key?

I am creating a table to store information about the placement of objects in a grid (game map). For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, (...
6
votes
2answers
7k views

PostgreSQL and default Schemas

Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas: Now from my understanding Schemas are like folders for organization, etc. So the ...
4
votes
3answers
15k views

Create table permission for a user in specific schema

I want to give Create , alter and drop permission to user A on a schema1 of a database. I guess this question has been already asked, and what i have found is to Grant Alter to the schema and also ...
4
votes
1answer
5k views

Grant select to dbo view without select on non dbo tables

SQL Server 2008 R2 We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database. We then granted it SELECT on a dbo ...
3
votes
1answer
6k views

shopping cart database schema

I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry ...
2
votes
1answer
192 views

Change tables from one user to another

I created some objects in MS SQL Server 2008 using SubSonic 3. It created them based on the user specified in connection string like: test_user.table1 but for some reason I am not able to test_user ...
1
vote
1answer
2k views

Changing Schema ownership and side effects

I am trying to understand why changing schema ownership is causing permissions to be revoked. I am having a hard time understanding schema ownership change. Here are the steps I have taken in ...