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
618 views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
0
votes
1answer
56 views

Convert multiple databases into schemas SQL Server

Due to a high amount of databases being created on my project and the rising cost per databases I had the following question: Is there a way to convert multiple databases in SQL Server into one ...
1
vote
1answer
21 views

PostgreSQL complaining about shared memory, but shared memory seems to be OK

I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..: WARNING: out of shared memory ERROR: out of shared memory HINT: You might need to ...
1
vote
1answer
121 views

Database Design for NoSQL Fantasy Baseball League

I am trying to create a proper schema to place scraped data into, using a MongoDB database. Currently it looks like Team { id : id, name : String, teamLink : String, username : ...
0
votes
1answer
111 views

Relation between multiple hierarchical taxonomies linked to a single entity

I am working on an online test application which supports multiple choice questions. I have created schema for taxonomy (classification) system to be used with questions. taxonomy(id, name, slug, ...
0
votes
1answer
272 views

MySQL DB structure with Main category and several nested sub categories

I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, ...
0
votes
0answers
8 views

CROSS JOIN and updatable model design for Ruby on Rails [duplicate]

I am trying to build the multi-level model in rub on rails to allow the following to happen: When a user creates a campaign they will select some categories and some metros from tables Category and ...
0
votes
1answer
10 views

Schema for a reservation system

I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding My ...
1
vote
1answer
20 views

What are some databases that are schema-full but not relational

What are some databases/datastores that are schema-full, but not relational? In other word I'm after some NoSql databases that are not schema-less. By schema-full I mean that, like in a traditional ...
1
vote
1answer
57 views

Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation)

Before you start suggesting this question for duplicate read it, I have searched and i haven't found any proper solution. So, i'm building a social networking site (I know that you are laughing ...
2
votes
1answer
53 views

select query on unique column AND alternate column of “aliases”

I have a MySQL table (~10,000 rows) with a pk id (int) column and a name (varchar) column (as well as a few other unimportant columns for this question). The name column contains unique values that ...
0
votes
0answers
50 views

Changing Schema ownership and side effects

I am trying to understand why changing schema ownership is causing permissions to be revoked. I am having a hard time understanding schema ownership change. Here are the steps I have taken in ...
2
votes
3answers
477 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
7
votes
1answer
312 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
0
votes
2answers
96 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
2
votes
1answer
282 views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
1
vote
1answer
21 views

MySQL Junction Table as Foreign Key?

I have several tables: company, customer, company_has_customer, and transaction. company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK. In almost all ...
1
vote
1answer
216 views

Should I save “document ID” as a String or Number in MongoDB

So I'm importing about a 100,000 documents into mondoDB. MongoDB already generates an Object hash but the data has a documentID that was used to identify records in the previous database. The IDs ...
0
votes
1answer
105 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
2answers
1k views

Oracle schema import is not importing all the tables present in the schema dump file

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not ...
0
votes
1answer
42 views

DB2 - how to safely blow away database on linux?

I'm using (I believe) 10.1.2, on CentOS 6.5 (for sure). The underlying problem I have is that the database upgrade package prepared in house isn't working for me, in at least the sense that the ...
1
vote
1answer
25 views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
1
vote
1answer
49 views

Handling user logins while using a multiple schema design to support multi-tenancy

I'm creating a web application (in PHP and PostreSQL) that will be used by organisations and their staff and I want to use multiple schemas to support multi-tenancy. There will be one database and I ...
1
vote
2answers
173 views

How can I integrate a new system to a design scheme I previously created?

This is a follow-up to a question I wrote a while ago, for reference, you can look it up here. I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown) Now that a ...
1
vote
3answers
60 views

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two ...
1
vote
0answers
616 views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
1
vote
1answer
91 views

Schema unusable after deleting another database

I just get a problem with a customer's SQLServer. I'm reading articles related to schema, owners and permissions, but I can't get it out. I have two DB : DB1 and DB2. I have a schema, which has the ...
0
votes
1answer
650 views

What's a good way to model user authorization to hierarchical data?

I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012). We're working with farm production information. We will have users who need to be authorized ...
1
vote
1answer
51 views

Need help with data model

Schema does not list all fields, but only the relevant ones User adds credits/funds to his account using paypal or Internet Banking (which has to be verified manually, and approved, so the 'status' ...
1
vote
1answer
57 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" ...
5
votes
2answers
41 views

How to enforce the structural constraints of rectangularly arrayed data?

(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and ...
2
votes
2answers
32 views

Schema for storing historical data on shared identifiers

When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need ...
1
vote
1answer
51 views

Does a Key-Value type table still needs a auto increment key?

Suppose that I have a very simple table to store variable-like data, like this: +-----------+-----------------+ | key | value | +-----------+-----------------+ | site_url | ...
0
votes
1answer
47 views

Schema for multi-account database

I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access. I'm putting together the schema for it and I have a few ...
4
votes
3answers
845 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.
0
votes
1answer
63 views

Database design Problem for the web-app

Hello I am in a problem of designing a web-app database schema. Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like ...
2
votes
2answers
1k views

Import Oracle schema data without losing modifications of stored procedures

I have this scenario: One huge (thousands of tables), complex production database, Oracle 8 One huge (thousands of tables), complex development database, Oracle 9 ( same structure as production ) ...
2
votes
1answer
216 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
38 views

modelling warehouse picking

What's the best way to approach modelling a warehouse picking system? To be precise, customers place orders which are processed by warehouse staff. A simple products and orders database isn't an ...
2
votes
0answers
53 views

Which is the better schema design for a voting system?

Imagine a q/a site or forum similar to stackoverflow. I'm trying to determine which of these two methods would be the better design for a voting system: (note: I would also like to keep record of all ...
1
vote
0answers
20 views

Can I include extra columns in a m->m mapping table? [duplicate]

I guess this is sort of an odd question, but consider this scenario. table classes (id_pk) table students (id_pk) table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore ...
1
vote
1answer
74 views

PostgreSQL: schema diff/patch tool

Consider the following setting: A production DB A dev db, on which schema changes are made to enable new features When the development of a new feature is complete, I have to manually update the ...
1
vote
2answers
52 views

Why WordPress use a column prefix? [closed]

I'm trying to learn some basic database design through open source projects. And look into the WordPress database schema, the posts table for example, you'll find something like this post_author ...
1
vote
1answer
21 views

How to put this particular constraint on this link table in MySQL? [duplicate]

I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table ...
5
votes
2answers
93 views

Creating a table with many optional fields

I am a developer (student) looking for some advice on what best approach should I follow while creating a table to model an entity that has a number of optional fields. There exists a need to model ...
1
vote
2answers
27 views

What's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL)

I'm designing a MySQL database that needs to contain a list of products. Those products, based on their category, will need to have a list of various properties. Let's say all products in category ...
6
votes
3answers
510 views

How can I organize my tables?

My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables. Is there any way to collect related tables in a folder ...
0
votes
1answer
90 views

date dimension OLAP cube design

I have a sales reporting system with incoming data like this date,editionID,outletId,Measure1,Measure2 2013-01-01,2,532,11,24 etc... I am new to designing data cubes (for the pentaho BI suite) and ...
0
votes
2answers
69 views

How to count all relationships in a database?

I am developing a web application, which stores data in a Microsoft SQL Server database. My project manager has to deliver a report to non-tecnhical higher-ups, and thinks that it will read impressive ...
1
vote
1answer
45 views

Schema design - Reference slightly different data in different tables

I'm trying to design a schema for a MySQL database and got stuck on the following. My database stores documents which should have relations with blocks of content. create table document ( `id` ...