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
721 views

Insert rows in a table, via a row-level trigger, activated after insertion in that same table

I have to design a schema and trigger system in SQL that will save words and their synonyms. I thought of this basic schema: create table words ( id int primary key auto_increment, word ...
1
vote
0answers
62 views

In terms of relational databases is a Relvar the equivalent to a Schema?

I've been reading up on relational databases and the term Relvar and schema keeps coming up. From my understanding: A relvar is a variable holding a relation. A schema is a logical definition of a ...
4
votes
1answer
159 views

Schemas and user permissions

When our custom, home-grown CMS creates data tables using the GUI, it creates them against the site-specific user schema (i.e. sitename) instead of the dbo schema. The issue is that our ...
52
votes
5answers
8k views

Is there a name for this database structure?

Noob question, sorry if it's too basic for this exchange. 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 ...
1
vote
1answer
49 views

Need Help in building database

I am developing an app that send messages (email and sms) to users. The db must also maintains the list of messages send to users. And also the status of messages send to user like send or failed. I ...
7
votes
1answer
122 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
0
votes
1answer
48 views

Design for periodic data cuts

I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy ...
0
votes
1answer
626 views

Migrating everything to do with a schema from one Oracle DB server to another

We have an old DB server, running Oracle 11g on Solaris. We also have a new DB server, also running Oracle 11g, but on RHEL. We want to move a schema to the new db - but we need everything it refers ...
1
vote
1answer
62 views

Schema copy from 9.5 V to 10.5

Using DB2 move utility db2move to copy schema and database from source to target server. The source server is Server1. The target server is Server2. Do I need to set up the TCP/IP port ...
2
votes
0answers
94 views

Schema for user drag and drop ordering

I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering ...
0
votes
0answers
513 views

LCK_M_SCH_M Blocking

I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other. They both (for some reason) acquire a schema lock when all they are doing is updating ...
0
votes
0answers
39 views

Accounting - Dimention table in mysql

I am designing a accounting and mini erp system. I use a date dimension table to track all date related data. In oracle we can have many schema for a database. so we could create each schema for ...
0
votes
1answer
98 views

ALTER TABLE blocking in MySQL 5.6

I have a question with a MySQL 5.6 master-slave setup. If I were to modify the master's schema with an ALTER TABLE SQL query, then when the slave tries to execute that ALTER TABLE statement too, would ...
1
vote
1answer
368 views

Database Design for NoSQL Fantasy Baseball League

I am trying to create a proper schema to place scraped data into, using a MongoDB database. Currently it looks like Team { id : id, name : String, teamLink : String, username : ...
2
votes
0answers
93 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to ...
0
votes
2answers
273 views

Is there a way to replicate certain part of collection in Mongodb

Is there away to stream certain part of collection in MongoDB or TokuMX from master server to another server? I need to duplicate only certain collection Or if it isn't possible, are there any other ...
1
vote
1answer
401 views

PostgeSQL 9.3: What is the difference between text and pg_catalog.text?

I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3). In order to keep the schemas ...
1
vote
1answer
130 views

can I give permission to insert into view, but not to referred table?

Here I have a view in public schema referring to the table in private schema: CREATE OR REPLACE VIEW public.products AS SELECT products.id, products.product_id, products.title, products.company_id ...
0
votes
2answers
66 views

Can I update only the needed changes in a database?

First of all, I apologize if you can't understand very well what I am saying and it can be edit for better understanding of my post. The next example is for better understanding of databases with ...
1
vote
2answers
48 views

Schema/Structure to handle secondary computed data

I'm trying to design a LAMP-based website for my pinball league, which plays competitive pinball every week in match play. One of the features of our league is that we convert raw scores to ...
2
votes
1answer
361 views

MySQL table indexes change location [closed]

Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?
1
vote
1answer
81 views

How can prevent writes to a Postgres schema for a period of time?

I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during ...
2
votes
1answer
2k views

copy data from public schema to another schema in postgresql

I'm creating a multi-tenant RoR app using PostgreSQL 9.3.5 running on linux (Ubuntu 14). I'd like to copy the data from my public schema to another schema. I tried: pg_dump -U my_username -h ...
1
vote
1answer
54 views

Simplest way to log in as Oracle user different from schema user and still see all tables,… of schema?

What is the simplest way to log in as user X and see all tables,... of schema Y as if they belonged to X? With an Oracle 11g database, I would like to log into the database using user X but all the ...
5
votes
2answers
419 views

How to store immutable, unique, ordered, lists?

I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists ...
0
votes
1answer
35 views

How to store external IDs from different sources?

I have a table of customers with a PK that we can use as an identifier for our internal system. These customers may belong to different companies, each of which may have an external ID in some ...
0
votes
1answer
4k views

SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables ...
7
votes
4answers
5k 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 ...
1
vote
1answer
211 views

Composite Foreign key to single primary key [duplicate]

How do I create a composite foreign key to a table with a single primary key? For example suppose I have the following tables: CREATE TABLE Branches ( BranchId UniqueIdentifier DEFAULT ...
0
votes
1answer
39 views

Re-posting schema

I'm building a social network very similar to Twitter, in which users can see a feed with content posted by users they follow. Users can like and re-post (i.e. "Retweet") posts. Re-posted content ...
7
votes
2answers
3k views

PostgreSQL: schema diff/patch tool [closed]

Consider the following setting: A production DB A dev db, on which schema changes are made to enable new features When the development of a new feature is complete, I have to manually update the ...
0
votes
0answers
64 views

How to determine a schema version at runtime

Is there a portable way or best practice to access a version number for a schema at runtime? Rationale: you have multiple systems (development, QA, etc.) where you push schema changes from one system ...
0
votes
1answer
32 views

Rescue truncated data from incorrect column type

I have mistakenly inserted a variable 18-20 digit variable into an unsigned INT() column. The data has been truncated to the max 32bit length so all my data is displayed as "4294967295" Is there ...
0
votes
1answer
30 views

Which foreign keys do I use if they are repeated

I have the following two tables company_relationships |ID|Company_ID|Affiliate_ID|<Relationship Info>| +--+----------+------------+-------------------+ jobs |ID|Company_ID|<Job Info>| ...
0
votes
1answer
36 views

Working out validity of table relationship

Question Background: In a quest to improve my skill set with TSQL programming I'm designing a 'simple' database schema for a zoo. In all I currently have 4 tables but am having an issue working out ...
2
votes
3answers
15k views

What is the difference between logical schema and physical schema?

While going through Database architecture I came across Schema's . I am very much confused with: Logical schema Physical schema How these two schema exist in the Database? How can these be ...
0
votes
1answer
64 views

to pivot or not to pivot - schema design question [closed]

I am implementing something similar to this schema http://www.databaseanswers.org/data_models/event_reservations/index.htm It occurs to me that the pivot table for Customer_Bookings would just keep ...
0
votes
1answer
28 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
0
votes
0answers
42 views

Database & Structure for SaaS reporting software

I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has ...
3
votes
1answer
15k 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 ...
1
vote
1answer
2k views

Updating mysql trigger via information_schema — access denied?

I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of: use information_schema; update triggers set ...
1
vote
2answers
93 views

Unique column values between tables

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical ...
3
votes
1answer
3k views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
0
votes
0answers
8 views

CROSS JOIN and updatable model design for Ruby on Rails [duplicate]

I am trying to build the multi-level model in rub on rails to allow the following to happen: When a user creates a campaign they will select some categories and some metros from tables Category and ...
0
votes
1answer
846 views

Schema for a reservation system

I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding My ...
1
vote
1answer
445 views

Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation)

Before you start suggesting this question for duplicate read it, I have searched and i haven't found any proper solution. So, i'm building a social networking site (I know that you are laughing ...
2
votes
1answer
211 views

select query on unique column AND alternate column of “aliases”

I have a MySQL table (~10,000 rows) with a pk id (int) column and a name (varchar) column (as well as a few other unimportant columns for this question). The name column contains unique values that ...
0
votes
0answers
266 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 ...
2
votes
3answers
608 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
1
vote
1answer
102 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 ...