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

0
votes
0answers
76 views

In regards to GS1 barcode - what size DB field are used?

In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
0
votes
1answer
248 views

Database schema design help needed

I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance. This application ...
19
votes
3answers
28k 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;
0
votes
2answers
857 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 ...
0
votes
1answer
38 views

Modify legacy table schemas that have no primary key

I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy ...
1
vote
0answers
439 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
1
vote
1answer
576 views

Compare two MySQL dump files

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
4
votes
1answer
578 views

Will table inheritance in PostgreSql suit this situation?

Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL. So my main aim is to have a Main schema in this DB, which I am going to use to ...
11
votes
3answers
21k 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? ...
1
vote
1answer
44 views

Multiligual database design

I assume that the best approach for many languages (5 and more) is concept based on: CREATE TABLE `language` ( `language_id` char(2) NOT NULL, `collation` varchar(64) NOT NULL, ...
1
vote
2answers
200 views

Extensible Asset Database Schema

Background I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an ...
0
votes
1answer
1k views

Granting permissions only on a set list of objects

I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in ...
2
votes
2answers
61 views

Best practice for database where the values can't change

Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The ...
0
votes
2answers
2k views

MS Access ER diagram

I have database scheme for Microsoft Access. I'm interested how I can convert the scheme into an ER diagram? Is there any tool that can do this based on scheme relations?
8
votes
4answers
575 views

Database Design - different objects with shared tagging

My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the ...
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. ...
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
117 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
2
votes
2answers
258 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
480 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 ...
7
votes
1answer
277 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
5
votes
2answers
106 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 ...
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 ...
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
1k views

Billing Module (Fee) for School Management System

I am working on a School Management software which includes module for billing of students i.e, fee collection from students. The fee is collected on monthly basis (12 times a year) and the total ...
0
votes
2answers
312 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 ...
1
vote
2answers
417 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 ...
4
votes
2answers
459 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 ...
2
votes
2answers
265 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
128 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
856 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 ...
1
vote
0answers
259 views

Powershell to dump database schema to text file

I want to be able to dump a database schema to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it ...
2
votes
1answer
121 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 ...
4
votes
2answers
192 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
43 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 ...
5
votes
1answer
21k views

How to transfer data using expdp and impdp commands?

I'm 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 expdp and impdp commands. I have ...
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 ...
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, ...
6
votes
3answers
475 views

How can I organize my tables?

My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables. Is there any way to collect related tables in a folder ...
3
votes
1answer
133 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 ...
3
votes
1answer
587 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 ...
4
votes
3answers
803 views

Are there any tools to go from ERD to DB design and vice versa?

I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa. For the record, the database i'm using is MySQL.
2
votes
2answers
280 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 ...
2
votes
1answer
122 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 ...
0
votes
0answers
106 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
1answer
436 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
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 ...