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
vote
2answers
2k views

SQL Server - backup / restore schema only (not a duplicate if that's what you're thinking!)

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
2
votes
1answer
3k 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. ...
2
votes
2answers
279 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
4
votes
2answers
510 views

Restore SQL Server database from .sql files

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git The ...
0
votes
0answers
46 views

Examples of a database with no consistency measures that ensures consistency through schema alone

To clarify, the database can be any type RDBMS, NoSQL/BigTable. However, it must not employ any usual consistency measures such as locks, or synchronization, but the inconsistency-invincibility must ...
3
votes
1answer
462 views

Dump PostgreSQL schema with foreign keys and referenced data

The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema. However according to the same documentation (read Note on ...
2
votes
1answer
586 views

copy schema across different physical databases in db2

Is there any way to programatically copy across schema (i.e. tables,functions,views) etc from one database over to another one. Assuming both are running same versions of DB2. I found a few utilities ...
5
votes
2answers
108 views

Relationship that are optionally more specific

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about. I currently have the following relationship. CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT ...
1
vote
4answers
2k views

Can't create user in SQL Server

Hi guys when I try to create user for my database I always encounter this kind of error. This is the sequence of task I done. I manage to create new login using windows authentication of SQL then ...
1
vote
1answer
360 views

Is it possible to fetch or update table data by table field type?

Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.
2
votes
2answers
5k views

how to add trigger in mysql workbench, without UDF, using EER design

I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema: ...
0
votes
2answers
74 views

What to have in the main table and what to have in meta_table?

I'm try to figure out the best scheme for the users table for my software. I'm trying to separate the main data and auxiliary data into 2 table called users and user_meta for the sake of extensibility ...
5
votes
2answers
779 views

Vehicle spare parts inventory datadase model

I am modeling a database to be used for vehicle spare parts inventory management and I am stuck... Some spare parts can be broken down from a kit into component parts. I would like to create a model ...
5
votes
3answers
1k views

Partition pruning with multiple date columns

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
0
votes
2answers
340 views

Relationships in a school database

I am designing database for a school app. Student is the main entity in the database. Student has relation with Parent & Guardian. I am confused as where to store references. As will student ...
4
votes
2answers
483 views

Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't ...
1
vote
2answers
444 views

Data warehouse support tickets

I'm starting to design a data warehouse for a company. The first questions we're trying to resolve are regarding their support ticketing system. My initial schema is as follows Now one of the ...
2
votes
2answers
281 views

Schema design for privacy settings in MongoDB

I have a set of documents that can have various privacy settings around them: They can be completed public (any registered user) can view them They can be viewable only by people following you (this ...
3
votes
1answer
130 views

Optimised Schema for City-to-City Distance Table

This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relations for the data represented below. I want to scale it vertically (new rows), instead of ...
5
votes
1answer
875 views

How does Postgres 9.2's streaming replication handle schema changes, and initial table setup?

I want to set up a backup database on the physical server which sits right under my primary physical database server. I'm using Postgres 9.2, and I want to use synchronous, streaming replication (for ...
2
votes
1answer
126 views

how to deal with foreign keys with fixed values (on a db-table-basis)

I have a control table that looks like this: CREATE TABLE `tbl_control` ( `tm_db_tbl` varchar(64) NOT NULL, `tm_key` int(11) unsigned NOT NULL, /* a bunch of other fields here */ PRIMARY KEY ...
10
votes
2answers
977 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 ...
4
votes
2answers
198 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, ...
0
votes
1answer
44 views

viewing Original Object under synonyms

I have certain synonyms in my schema which represent views from another schema. Now in oracle sqldeveloper, I am unable to see the actual SQL for those views but I see only an SQL like: CREATE OR ...
4
votes
1answer
150 views

Always being logged in as SA and not Windows account

First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server. I have recently started a project working with SQL Server 2008 R2. I have access to the database ...
2
votes
1answer
2k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
0
votes
2answers
1k views

SQL Server Database Diagram in SQL Management Studio creating multiple schemas

I'm using a multiple schema approach (Multi-Tenant arhitecture) in the same DB and it's working great. I can use EF bounded contexts and split domains appropriately. According to MSDN, ...
2
votes
2answers
1k views

One database with two schemas: cross-schema View permissions issue?

I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and a new one called Test. I have a view in the Test schema called View1 that selects from dbo.Table1. Both schemas are ...
3
votes
1answer
135 views

Referential Integrity in schema, cascading the renaming

In phpMyAdmin, while renaming a column/table which is referenced by some foreign key, I get an error. I can not rename a column until I remove all its relations. There should be facilitated integrity ...
2
votes
1answer
128 views

Ranking System with Different Categories

I want to create a system where the user can be ranked by several different factors. For example, the user competes in the following categories Wins Points I need to know what the position of the ...
3
votes
1answer
615 views

Distinguish between schema and database

I had the error cross-database references are not implemented: scan.location.alias with this PL/pgSQL code: DECLARE v_tmp_host scan.location.alias%TYPE; v_ip_address character varying; BEGIN ...
4
votes
1answer
188 views

250 billion rows - computing interconnectedness on a really huge scale

I have a table of approximately 700k rows, each identified by a unique itemnumber. Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
2
votes
2answers
290 views

Please review my first thought-out attempt at a database schema

I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL. I would like, if someone has time, to review my layout and ...
0
votes
0answers
107 views

Database (open source) for complex queries (for analytics) like these:

In an app, I'll be storing information about events, specifically, session_id url time type data: foo: bar bar: foo The data will basically be a hashmap (need to support arbitrary types / names ...
3
votes
2answers
3k views

List primary keys for all tables - Postgresql

Is there a query that will do that? I found some queries that can do this for one table, but I wasn't able to modify it so I can see: tablename | column | type
1
vote
1answer
182 views

Whitelist-based SQL Server schema setup

My group has access to a single database on a SQL Server instance. We have a number of different people in our group, each working on separate projects. In trying to determine how to segregate ...
1
vote
2answers
118 views

Having multiple refs in an intersection table

I have two tables BOOK and JOURNAL, I want to store their authors in an intersection table called AUTHORS. Is it acceptable practice to have mulitple reference id columns (kind of FK's I think) to ...
0
votes
1answer
117 views

unenforced constraints/possible duplication in my database design?

I am trying to store information about a school and the class timetable in a database. The outline is as follows: 'A subject has ~20 lessons, each of which needs to have it's description stored in ...
3
votes
1answer
2k 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 ...
2
votes
3answers
877 views

How to find out what caused a schema change?

I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on ...
0
votes
1answer
208 views

How to simplify schema for multi database / hundreds of millions of rows data set

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started ...
2
votes
1answer
206 views

MySQL: Show Unique key definition by name

I get the following error: ERROR 1062 (23000) at line 1901: Duplicate entry '3-3820-50-0' for key 'UNQ_CAT_CTGR_ENTT_DTIME_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID' How can I find out which table ...
1
vote
3answers
141 views

Database schema - performance v correctness information

I have a database that stores information about the users of my website (username, password, etc). I also store information about their activity on the site. For example, I have a large number of ...
1
vote
2answers
203 views

What are the pros and cons of this schema for flashcards app?

I am planning a flashcards app with the following schema: user(id, name, email) flashcard_group(id, user_id, group_name, group_tag) flashcard(id, group_id, question, solution) guess(id, user_id, ...
2
votes
1answer
785 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 ...
3
votes
1answer
118 views

What mechanisms do you use to notify third parties of future schema changes?

How do you notify third parties of future SQL Server schema changes other than through external documentation? I've started implementing a deprecation warning extended property on objects that will ...
1
vote
3answers
324 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
4
votes
2answers
3k views

MySQL Database Schema for Train Timetables

I am interested in what a MySQL database schema would look like for a train timetable. Generally this is presented in table format as the final result. Train No. 11111 22222 11111 Train Day ...
2
votes
1answer
930 views

How do I persist recurring events?

I've been tasked with designing a system to recur events. Events may recur daily, hourly, monthly or yearly. Users should be able to schedule events "every 3rd Wednesday of the month" or "The 1st of ...
1
vote
1answer
520 views

Replicate/Mirror SQL Server schema to slave databases

I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database. MASTERDB will be always empty, as I only need the schema. No data is going to ...