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
1answer
309 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
399 views

SQLite3 simple database design question

I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database. The ...
3
votes
1answer
196 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 ...
2
votes
0answers
166 views

Oracle sqldeveloper Database Diff - not seeing indexes/sequences

I am using Oracle SQL Developer's database diff tool to compare schemas. (SQL developer version : 3.2.09) It works great on some environments, but in one, I can only see differences in tables - the ...
2
votes
1answer
179 views

Schema for 1 <-> 0..1(+), many related but only one primary

Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My ...
1
vote
1answer
124 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 ...
2
votes
1answer
844 views

PostgreSQL schemas and search_path

I'm reading Oreilly's PostgreSQL: Up and Running, and in the section Organizing Your Database Using Schemas it says the search_path may be set at the database level: For example, if we wanted all ...
4
votes
2answers
355 views

Designing an aggregation schema

The only thing closest to what I'm developing is Facebook's activities log for users. So I figured if I could ask about how FB might possibly implement such a feature at the database layer, then it ...
2
votes
3answers
282 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
7
votes
1answer
460 views

Concept of Schema in PostgreSQL

I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design. Why should I use it? Can it affect me in future if I decide to not think ...
2
votes
1answer
215 views

Is having all entity tables inherit from one main entity table a bad idea?

My co-worker is designing a schema where most entities (like contacts, quotes, tasks) inherit from an "entity" table, which keeps track of things like which tenant owns the entity, and chagnes to the ...
4
votes
2answers
1k views

database schema design for school system

I am trying to create a database to store information about a school. I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes ...
0
votes
0answers
148 views

Schema for Google Docs-like sharing

Am trying to design a nicely normalized schema for sharing entities in a database, with good referential integrity. It should work similarly to Google Docs sharing. Here is what I have so far: ...
1
vote
3answers
353 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 ...
6
votes
1answer
852 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 ...
2
votes
1answer
288 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 ...
2
votes
1answer
352 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 ...
1
vote
2answers
396 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 ...
2
votes
2answers
234 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
279 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, ...
2
votes
1answer
62 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. ...
10
votes
2answers
368 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 ...
1
vote
1answer
57 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
291 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
543 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 ...
0
votes
1answer
39 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
36 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 ...
-3
votes
2answers
324 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?
1
vote
1answer
331 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 ...
2
votes
2answers
158 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 ...
0
votes
1answer
308 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
59 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
781 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 ...
3
votes
1answer
437 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 ...
6
votes
2answers
323 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
1k 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
691 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
491 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 ...
6
votes
2answers
616 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 ...
3
votes
1answer
482 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 ...
15
votes
1answer
3k 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 ...
3
votes
1answer
2k 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 ...
2
votes
2answers
81 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 ...
2
votes
1answer
501 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
121 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 ...
-1
votes
2answers
320 views

Diet and exercise schema

Pardon the brevity but: I am currently working on a project which requires users to create a 12 week diet/exercise regime. I basically need to store 3 meal options for each day which can be made up of ...
2
votes
3answers
401 views

Physical Address Schema

I'm looking at adding addresses to an existing database, and am trying to work out the advantages and disadvantages of the following methods (and if there are any I've overlooked). Store the address ...
4
votes
1answer
1k views

Single database schema or multiple schemas?

I'm designing a website for an annual event. The events are similar but mostly independent year-to-year. I see 2 possible designs: A single schema with most tables having an EventID column to tag ...
12
votes
4answers
7k views

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
0
votes
1answer
141 views

What are the first necessary actions needed to help secure a database's schema & data? [closed]

This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions. It seems like there should be a good checklist out there for all to agree on ...