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
3answers
143 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 ...
2
votes
1answer
984 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 ...
6
votes
4answers
6k views

Is there a tool to check if my database is normalized to the third normal form?

I learned about normalization recently, and understand how important it is when implementing a new schema. How can I check if my database is 2NF or 3NF compliant ? Manual review is a sure option, ...
2
votes
3answers
995 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 ...
1
vote
3answers
375 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 ...
1
vote
2answers
230 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
257 views

Best embed/reference/field strategy for many inserts (mongo)

I'm building a gaming backend with Mongo, and have some issues on how to best design the schema to maximize performance and database size. My models: User Match -ReferenceMany (User) ...
0
votes
1answer
2k 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
3answers
2k views

Workarounds for setting default schema for domain group?

I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated ...
5
votes
1answer
745 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. ...
2
votes
0answers
243 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 ...
3
votes
1answer
122 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 ...
0
votes
1answer
222 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
1k 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 ...
5
votes
2answers
902 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 ...
-1
votes
1answer
290 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
286 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 ...
4
votes
1answer
523 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 ...
1
vote
1answer
63 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
3answers
307 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 ...
4
votes
2answers
553 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 ...
3
votes
1answer
2k 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 ...
2
votes
1answer
254 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
3k 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
208 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
1answer
477 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. ...
5
votes
1answer
3k 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
214 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 ...
7
votes
1answer
1k 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
140 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 ...
4
votes
1answer
150 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
437 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
313 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
527 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 ...
41
votes
5answers
4k 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
1k 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
2k 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
244 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
369 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, ...
3
votes
2answers
5k 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
66 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
58 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
375 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
1k views

Comparison Tool for MySQL data base schemas [closed]

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
459 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 ...
0
votes
1answer
40 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 ...
1
vote
1answer
452 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
423 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 ...
6
votes
3answers
12k 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 ...