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).
3
votes
1answer
150 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
389 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
85 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
572 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
248 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
990 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
62 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
0answers
73 views
What does a multidimensional cube's internal structure look like?
We all know what a relational data model looks like, and most know what a star schema looks like, but what about the structure of a multidimensional database (OLAP cube)? I am assuming it is some sort ...
5
votes
2answers
445 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
316 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
151 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 ...
3
votes
2answers
223 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
152 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
124 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
...
31
votes
5answers
1k 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 ...
3
votes
1answer
79 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 ...
3
votes
1answer
365 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
60 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 ...
8
votes
2answers
610 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 ...
3
votes
2answers
116 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
17 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
4k 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 ...
3
votes
1answer
142 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
701 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
320 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, ...
0
votes
0answers
23 views
How can I represent a set of user-defined attributes in a schema file for input into a DKNF db?
I have a hyper-normalized database in DKNF. Shown below are the entities in the DB (at this revision).
AttributeKey implements the key part of the key-value pairs for ObservationValue on ...
2
votes
2answers
501 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
100 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
72 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
230 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
130 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
2answers
346 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
158 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
90 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
935 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
0
votes
2answers
2k views
In Oracle how do you move a Schema to a different Tablespace?
I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?
1
vote
1answer
128 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
101 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
102 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 ...
2
votes
1answer
672 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
233 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
136 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
110 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
122 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
126 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
552 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
87 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
161 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
2k 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 ...
0
votes
1answer
476 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 ...
