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

0
votes
1answer
43 views

to pivot or not to pivot - schema design question [closed]

I am implementing something similar to this schema http://www.databaseanswers.org/data_models/event_reservations/index.htm It occurs to me that the pivot table for Customer_Bookings would just keep ...
0
votes
1answer
28 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
0
votes
0answers
21 views

Database & Structure for SaaS reporting software

I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has ...
2
votes
1answer
7k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...
0
votes
2answers
50 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
1answer
151 views

Updating mysql trigger via information_schema — access denied?

I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of: use information_schema; update triggers set ...
1
vote
2answers
77 views

Unique column values between tables

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical ...
1
vote
1answer
108 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 ...
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
33 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
26 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
99 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
84 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
77 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
500 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 ...
1
vote
1answer
41 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
57 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
111 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
3answers
632 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
694 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
98 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 ...
1
vote
1answer
58 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
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" ...
5
votes
2answers
53 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
38 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
53 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
67 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
980 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
64 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 ) ...
0
votes
1answer
43 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
81 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
2answers
58 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
23 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
103 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
44 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
2answers
601 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
175 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
82 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
52 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` ...
0
votes
1answer
53 views

Trigger on a Schema

Is it possible to create a Trigger on a Schema? I have to audit the changes on my tables. But I have a lot of tables and I don't one to create a trigger on them one by one. --Database level trigger. ...
0
votes
2answers
96 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
0
votes
3answers
275 views

What is the EXPORT privilege on ORACLE

I have created a user say A. I want to export some of my schemas using A user. For example, I have B and C users. I want to export B schemas using A user. Which grant do I have to use? I am using ...
0
votes
0answers
34 views

Export sql server table connections schema

i am using RazorSQL to connect to a Microsoft SQL Server database. I am looking for a way to visualize the tables connections schema. Do you have any idea how I can export such a view?
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
2answers
752 views

DB2 (LUW) equivalent for Oracle “drop user cascade”

This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows) On oracle, we have an os user (on ...
1
vote
2answers
118 views

Does this medical database look correct?

I'm pretty new to databases and I'm trying to create some schemas. I've been working away at this one for a while, but I can't help feeling that it doesn't look right. Essentially I have the ...
0
votes
0answers
94 views

How to share Users table between Clients and Customers?

I have a rental system where the system admin can add clients and their users. Each client's user can manage products, categories, orders etc., as well as their own customers. However, while ...
0
votes
0answers
101 views

Cassandra: unstructured document storage?

I'm fairly new to NoSQL, but fairly experienced with SQL-based relational databases. We are evaluating Cassandra for one of our applications. I've seen where Cassandra is claimed to be able to store ...