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).
1
vote
1answer
211 views
how to avoid duplicated column in two different tables?
OK, I'm inexperienced in terms of database design, so hopefully my question isn't too silly.
I have 2 tables users and user_profiles these two tables has one-to-one relation between them. ...
4
votes
1answer
169 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 ...
3
votes
1answer
174 views
Database Relationships overview appreciated
I am helping to create a database via salesforce for storing information about Non-profit organizations.
It has primarily 4 entities in the schema: Agency, Program, Project, and Contact, where Agency ...
5
votes
1answer
490 views
Do schema changes “break” SQL Server 2012 AlwaysOn or are they handled transparently?
My organization is planning to adopt SQL Server 2012 AlwaysOn and I am trying to understand what impact (if any) it will have on our application upgrade process.
We release application updates on an ...
1
vote
1answer
100 views
Default Schema Problems on server
When i connect to SSMS on my server through remote desktop and query my database using:
SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID()
I am getting the correct results ...
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
202 views
Database schema size result differs from file size when exported
I used this query to find out my database size
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY ...
2
votes
1answer
252 views
.NET SQL Server Authentication Schema issue
I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL ...
1
vote
2answers
230 views
MySQL table design / architecture, table is too big
I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table.
I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in ...
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 ...
2
votes
1answer
258 views
Under what conditions are polymorphic associations used? What are the alternatives?
As I understand it, polymorphic relationships among tables in a database allow one table to have one foreign key that references many tables.
I have found that tags are a common application of this ...
1
vote
2answers
569 views
In Oracle, how can I give one user privileges to alter the schema of another user?
I have an Oracle DB (10.2), and I have a consultant (Dogbert) that I want to do some work on it. In particular they need to alter the table structures of tables in a schema (we'll call it Wally) that ...
2
votes
2answers
199 views
creating a database scheme for storing information about an item, which option is better?
I have a database which stores information about various items. In particular, for each item I need to store the name, type and color. Each type can have a various amount of different colors. For ...
1
vote
1answer
211 views
designing server side database to store user-specific data
I have a database with a number of quiz questions and answers. My basic design consists of these two tables
questions (question_id, question_string);
answers (answer_id, answer_string, ...
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?
2
votes
1answer
55 views
Creating a schema for various sizes of varchar
I am redesigning a database schema for an application I am working on, and one of the major changes I am adding is the ability to dynamicly add new "fields" into a form based administration area.
...
1
vote
1answer
47 views
What steps are required to move data from a custom schema in one database to the default schema in new database
Lets assume for the sake of argument that I have the following tables in a single SQL Server database.
mySchema.Users
mySchema.Products
--
secondSchema.Users
secondSchema.Contracts
...
0
votes
1answer
184 views
How to extract a whole database schema using only ODBC access?
I need to migrate an old proprietary database (custom database based on a Btrieve engine) to a newer system . All I have to access this database is an ODBC driver that works pretty well. When I ...
3
votes
3answers
262 views
Comparison Tool for MySQL data base schemas
We'd like to update an old database schema for a new software release that requires changes in database schema (additional colums, column renames, table renames, datatypes, ...). Is there any ...
1
vote
3answers
290 views
shoud we normalize user profile table with fields like interests,musics,videos,sports, etc?
I've read in an article about social networking database schema and saw that they've used varchar for those columns that I mentioned in the question title. It's not normalized! Isn't it better to have ...
1
vote
1answer
36 views
Linking table or column key
Simple structure:
have a table PAGES - which stores information about specific landing pages
working on developing a GROUPing concept for the pages, where we are saying that a page may or may not ...
2
votes
0answers
34 views
Splitting a wide table [duplicate]
Possible Duplicate:
Database Design: New Table versus New Columns
I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important ...
1
vote
1answer
228 views
When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert?
I want to create a snapshot of an Oracle schema that I can revert back to after testing. Impdp and expdp seem to be the best fit as far as how I'd like to accomplish it, but the problem is that impdp ...
0
votes
1answer
228 views
Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases
I'm looking for software tools to move database schema for Oracle, SQL Server and Netezza. We use Oracle for our ODS, Netezza for our Data Warehouse (for it's bulk loading capabilities) and SQL ...
3
votes
1answer
1k 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 ...
5
votes
3answers
274 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 ...
2
votes
2answers
142 views
A Huge Database: Picking the right schema and data model
We want to store some genomic variant data but there are some problems, more important ones like problem of the data's immense size and variability.
1) Variant data can be huge. For example, a single ...
3
votes
1answer
58 views
Why isn't there a way to label a column as “keyword”
In a typical star schema, you end up with a bunch of columns that are an ID into some lookup table, where the lookup table is pretty much just this:
create table hostnames (
id primary key,
...
1
vote
2answers
407 views
Changing the default schema of a user
In order for an application user (APP_USER) to use another schema (SCHEMA_OWNER) without the need for prefixing queries with the schema name, I have implemented a logon trigger that changes the ...
1
vote
1answer
220 views
PostgreSQL schemas, select same table from all schemas
My app is used by about 80k users every month. I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection).
My app allows users to search other user's profiles, via:
...
3
votes
1answer
382 views
noSQL ACID and consistency for banking
We are building a real time banking platform and app.
Our prime concern is safety and consistency of our data across multiple db servers. We need transactions and we can't afford to risk power ...
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 ...
-3
votes
2answers
227 views
How can I generate Cassandra data model from a MySQL schema?
Is there a tool for generating a Cassandra model from a MySQL database?
6
votes
2answers
253 views
What is the name of this schema pattern
Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details.
Essentially, the pattern consists of one database which is 3 ...
3
votes
2answers
414 views
How to INSERT row in another database/schema in MySQL
Let's say I have the following database/schema setup with the following tables:
SCHEMA_1 (default):
TABLE_1
SCHEMA_2:
TABLE_2
How do I insert rows in another schema's ...
1
vote
1answer
374 views
How to create a train schedule in SQLite3?
I am interested in what a SQLite3 database schema would look like for a train timetable.
Generally this is presented in table format as the final result.
Sample Schedule for M to F
STATION A ...
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 ...
4
votes
2answers
412 views
What's a good relational structure for units and complex unit conversions?
My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
2
votes
1answer
347 views
Write to different schema with own tablespace from another account
Say I have created these two accounts:
CREATE USER u1 IDENTIFIED BY "&&u1Password"
DEFAULT TABLESPACE u1tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON u1tbs
QUOTA 256M ON ...
2
votes
1answer
338 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 ...
4
votes
1answer
1k views
Storing XML in varchar(MAX) or use SQL-XML
I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
9
votes
2answers
283 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
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
2
votes
2answers
73 views
Is it safe to use “Safety” as a schema name?
I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does ...
12
votes
1answer
2k 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 ...
2
votes
1answer
422 views
Separate Schemas by Industry or by Application functionality?
I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web ...
2
votes
0answers
117 views
Schema-less/flexible + ACID database? [duplicate]
Possible Duplicate:
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 ...
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
1answer
661 views
Drop auto increment hack w/o alter table?
I was looking around for a quick way to remove an auto increment from the definition of a primary key. As best I can tell the only way to do it is w/ an alter table or dumping all the data into a new ...
1
vote
3answers
594 views
SQL Server 2008 R2 Express Edition - generating database create script
I need to script a database schema out to T-SQL for inclusion source control. The Express version of SSMS doesn't seem to let you script the entire database, just individual tables. Are there any free ...
