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).
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
481 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 ...
1
vote
1answer
229 views
Replicate/Mirror SQL Server schema to slave databases
I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database.
MASTERDB will be always empty, as I only need the schema. No data is going to ...
0
votes
1answer
372 views
Db design for a quiz with variable number of answer choices, and each worth different score in a different category?
I'm working on a question/answer project and having a hard time coming up with a suitable database structure.
Each question can have from 1 - "n" answers, and each answer is associated with one of ...
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:
...
5
votes
1answer
206 views
Converting Database from GUID to Integer Keys
I have a medium sized database (~100 tables) that uses GUIDs as primary keys in all tables.
In one of our most active tables the GUIDs are causing an unnecessary increase in storage requirements. ...
-1
votes
1answer
222 views
Database for an application similar to Chatroulette
I am making an app that is similar to Chatroulette.
When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could ...
2
votes
1answer
259 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
1answer
212 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
288 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
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 ...
2
votes
0answers
126 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
121 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
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 ...
1
vote
0answers
31 views
Is there a way to create a re-usable base table definition in InfoSphere Data Architect?
Our company has chose to use IBM's InfoSphere Data Architect (IDA) for data architecting regarding our data warehouse and data marts.
The data architects originally designed several "re-usable" ...
2
votes
1answer
254 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 ...
3
votes
2answers
240 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
228 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 ...
6
votes
1answer
368 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
171 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
855 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
102 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
291 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 ...
5
votes
1answer
493 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
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 ...
2
votes
1answer
203 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
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 ...
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, ...
4
votes
3answers
1k views
Are there any free online collaboration database schema tools?
I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
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.
...
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 ...
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
263 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
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 ...
-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?
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 ...
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 ...
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 ...
3
votes
1answer
663 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 ...
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
409 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
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 ...
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
421 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
376 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
350 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 ...
