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

2
votes
1answer
1k 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
966 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, ...
2
votes
2answers
1k 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
129 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
121 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
547 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
185 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
775 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
269 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
106 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
2k 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
1
vote
1answer
177 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
118 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
115 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 ...
3
votes
1answer
2k 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
816 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
199 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
202 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
139 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
189 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
722 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
116 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
301 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
3k 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 ...
2
votes
1answer
885 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
463 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
1k 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
306 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
479 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
281 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 ...
3
votes
1answer
832 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
382 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
443 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
202 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
184 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
215 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
130 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
1k 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
421 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
290 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
511 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
232 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
2k 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
171 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
389 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
973 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
303 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
389 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
448 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
238 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 ...