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

Review database schema / thought process

Please review my thoughts below. It is written in an authoritative style because it is the most succinct, not because I am competent. Please poke as many holes as possible. I am trying to create a ...
0
votes
1answer
128 views

Need some expert eyes to look at my schema

I am trying to make an online site for local movies. Will be written in PHP and will be using MySQL as the database. What I want is to create a mini IMDB type site, with movies, people(casts/crew), ...
2
votes
0answers
168 views

Database Design - Multiple Dbs or Single - Security & Data [closed]

I've hit a brick wall with this and just cannot make a decision so want to throw this out there and see if there are any issues I have'n't considered. Basic Topography I have a system that currently ...
0
votes
1answer
548 views

Split MySql table into two tables with One to many Relationship

I have an old database table in MySql. It is used to store the orders from online ecommerce. Simplified diagram looks like this: +----+------+-----------------------+-------+--------------+ | id | ...
0
votes
1answer
69 views

Unable to check is schema is present first

I've created a SQL script that will release a process to numerous SQL Server instances so I want to check if a paticular schema is present before creating the schema. I'm using the below and it ...
2
votes
0answers
63 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to ...
2
votes
1answer
470 views

Database deployment script for SQL Server 2008 has different behaviors

Using Visual Studio 2008, I have compared schemas between remote SQL Server database with scripts hosted in a TFS server. The goal was to generate a migration script that would update the remote ...
1
vote
1answer
2k views

Script not allowed to create schema objects when generating tables despite db_owner role

I am looking to make a "schema only" (no data) copy of a database on my local server. I used Tasks -> Generate scripts to script the tables from my existing database and then after creating the new ...
2
votes
1answer
437 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
1answer
48 views

Create menu from multiple meals relationship

I have the following relations: Meals(meal_id, name, cuisine, ingredient) Users(user_id, name, username, password, email) Chefs(chef_id, name, username, password, email) I want to create the ...
0
votes
1answer
358 views

Airport Database Design guidance required

I am trying to make a generic database for airport management and DBMS would be oracle 11g xe. Since, i am amateur and still learning so i have tried to kept the problem statement simple with limited ...
3
votes
1answer
706 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 ...
2
votes
1answer
94 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
1
vote
3answers
2k views

MySQL schema for recurring events - Query by specific date

I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well. I'm just about to start putting together a ...
0
votes
1answer
39 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
0
votes
1answer
397 views

one to zero or one relation strategy

For a 1 - > 0..1 relationship (Let's say a product can have none or 1 product_price) What are the differences between this 2 scenarios. When one should be encoraged over the other or is anyone bad ...
1
vote
1answer
109 views

mongodb Date strings and other issues

I asked a question on Stackoverflow, and was told that my schema was way off. I am using mongodb to store whether or not a log file exists. The log file contains info for an hour. I'm not actually ...
1
vote
1answer
242 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 : ...
2
votes
1answer
116 views

Saving passport information [closed]

For starters: Our company is based in Germany and thus is based on German law. Germany now has adopted a new act regarding anti-money laundering and which information needs to be stored. Now we're ...
1
vote
1answer
80 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' ...
0
votes
1answer
252 views

understanding (and modelling) grocery shopping lists

I am having a hard time understanding how multiple product orders are mapped out. What is the best way of modelling a shopping list for multiple customers? I am developing an C# application that ...
3
votes
2answers
3k views

Postgresql: Query across schemas

I have multiple schemas that I need to query across. The schemas are not know in advance for example: U111 U222 U333 I need to select across these three schemas from a specific table in each of ...
5
votes
2answers
442 views

Searching 500 million records of binary data

I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read ...
1
vote
0answers
75 views

Do supertables produce bottlenecks?

There are several alternatives to using polymorphic associations and one of them is to use the so-called "concrete supertable". From my point of view, this is the neatest solution and is pretty easy ...
0
votes
1answer
386 views

How can I Automate MongoDB schema design from existing MySQL Database and Logs?

I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your ...
0
votes
0answers
45 views

Multi language where this feature never existed

I need to implement multi language system in an existing system that was not developed for this. The number of entries in the tables that will hold data that has to be translated will not exceed 5 ...
2
votes
1answer
401 views

Is there any way MySQL can list relationship cardinality between two entities?

I am working on the project where I have to analyse the MySQL schema. As a part of schema information, I want to list down relationship cardinality between different relations. Does MySQL stores ...
2
votes
2answers
1k views

Dynamically change schema to accommodate custom fields!

I am working on building a sort of a cms for a particular project. Nothing that will have a large volume of records in any table. Within 6 digits max. I was thinking to create the default schema for ...
1
vote
2answers
179 views

MySQL 5.5 to 5.6 Upgrade. Will data remain the same?

We are considering to upgrade to MySQL 5.6 this summer. I would like to know if our data will be changed in any way once we perform the upgrade? I've been reading and I found that mysql_upgrade ...
0
votes
0answers
286 views

Review a database schema for a design approval system

I'm working on my first web application that will utilize a database. The basis for the application is a design approval system. I work for a company which mainly produces print and online marketing ...
1
vote
1answer
675 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, ...
2
votes
3answers
9k views

What is the difference between logical schema and physical schema?

While going through Database architecture I came across Schema's . I am very much confused with: Logical schema Physical schema How these two schema exist in the Database? How can these be ...
0
votes
1answer
150 views

Help With MySQL Computed Columns/Auto Updating Field Based On Average

I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this ...
3
votes
1answer
159 views

Help with “Flexible” vs Fixed Tables

We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this: There are only a couple of us on the project, and our boss dropped this ...
1
vote
0answers
310 views

Lossy Decomposition meaning

Suppose I have a relation R=(A,B,C,D) and I decompose it into: R1=(A,B,C) R2=(A,D) then if it is said that the decomposition is lossy,then what is the meaning of that statement?I have an ...
1
vote
2answers
142 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 ...
1
vote
1answer
424 views

Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one. I am changing a column while not changing its alias so consumers will be unaffected. If I were to drop all my other views ...
0
votes
0answers
69 views

How should I restructure a table that contains multiple columns that contain similar, but not same data?

I currently inherited an InnoDB table that describes a product. In the table there are a few columns that describe the maximum height at which the product may be mounted. These are the columns: ...
2
votes
2answers
912 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 ...
3
votes
2answers
243 views

Is there a well-known name for this “poor man's ref. integrity” schema design pattern?

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin ...
10
votes
4answers
2k views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
0
votes
2answers
128 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 ...
1
vote
0answers
113 views

MySQL Workbench Forward Engineer from only the current diagram

I want to create a test database from a huge schema in MySQL, but when I try to do Forward Engineer from a Diagram with about 6 tables, it try to create all the tables, views and routines groups that ...
1
vote
0answers
388 views

MySQL Space efficient way of storing indexed key-value pairs associated with table

We're storing transactions in an accounting system using two tables, one contains the general description of the particular transaction (the other contains the entries, one for each account - which ...
1
vote
1answer
155 views

Scheme review/Normalization level/query optimization

So I have a relatively complex database I'm designing - basically full in-house accounting setup. In one section I have some concern that I'm over-normalizing; the 'directory' - I'll example the ...
3
votes
2answers
57 views

Best design for a many:many relationship issue

We have three types of entity, each with their own table: table:A(a_id), table:B(b_id), table:C(c_id) Each of these entities can have any number of email addresses and linked Twitter accounts. ...
0
votes
1answer
176 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
304 views

Automatically generate ALTER TABLE to syncronize schema on PostgreSQL

i Have 2 different database schema in PostgreSQL sql file from pg_dump, is there automated way to generate alter table commands so it would be in sync, without inspecting one by one? this is the ...
0
votes
1answer
241 views

Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema?

So I have a login that has sysadmin rights on the db server. However, it's not able to access database objects under a custom schema that was created (eg. it cannot select from tables with ...
1
vote
1answer
438 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 ...