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
votes
2answers
24 views

query multiple unrelated tables

Just learning mysql and db so guess this is a general schema design question. Say i have 10 different types of widgets and each widget transaction has a date, amount and 8 other unique columns to that ...
2
votes
1answer
64 views

SQL Server many-to-many relationship, row reuse?

Below is your typical many-to-many relationship. Pretend there are 2 tasks that have the exact same Tags. because of foreign key/primary key relationship constraints, there is no way for the rows in ...
1
vote
1answer
37 views

What will GRANT <privilege> ON ALL TABLES IN SCHEMA <scheme> TO <role> do?

It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command. But, will it make it permanent for the schema that on all tables within it, ...
5
votes
2answers
259 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
88 views

How do you think in NoSQL? [closed]

I'm from the pre-nosql era and am rather comfortable with SQL. However, I cringe whenever someone suggests using a NoSQL database for the "upcoming" project. There are some major problems that I'm ...
0
votes
1answer
29 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;
2
votes
1answer
57 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 ...
2
votes
1answer
114 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 ...
0
votes
1answer
57 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), ...
1
vote
2answers
95 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 ...
1
vote
0answers
112 views

Neo4j vs mongodb [closed]

I want to store data of parents,teachers,students,subjects and the respective marks. All these entities are highly interlinked. Different schools have different type of data and each student will have ...
2
votes
0answers
87 views

Database Design - Multiple Dbs or Single - Security & Data

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
77 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
41 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 ...
1
vote
1answer
50 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 ...
2
votes
0answers
35 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 ...
1
vote
1answer
502 views

Oracle schema migration to new database with zero downtime

I have a problem: I have two production schemas on one database each serving two different appplicaitons. I need to migrate (move) one schema to an entirely new database that will be dedicated for ...
4
votes
3answers
7k 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 ...
2
votes
1answer
190 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 ...
0
votes
1answer
200 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 ...
1
vote
1answer
167 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 ...
0
votes
1answer
37 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
94 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 ...
4
votes
4answers
2k views

Are there any free online collaboration database schema tools? [closed]

I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
6
votes
6answers
5k views

Is there any good tool for making Database Design and Prototypes? [closed]

I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
2
votes
1answer
111 views

To normalize or not to normalizde? (10 fields)

I'm helping someone to design a database and for a specific case I'm not sure. I have 10 fields - this will never change - that will always be filled and used in every query that could be coming up. ...
2
votes
1answer
72 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
481 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
2answers
390 views

Multi-Tenant (SaaS) Database Schema with Sharing

Guys I just need your opinion which is the best approach to design a schema of a database for a SaaS application. I want it all on a single database, At first I think I can simply put a context_id ...
3
votes
1answer
78 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 ...
0
votes
1answer
70 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 ...
2
votes
1answer
81 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 ...
0
votes
0answers
34 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
82 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 ...
2
votes
2answers
430 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 ...
2
votes
2answers
4k 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?
1
vote
0answers
57 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 ...
2
votes
2answers
175 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 ...
0
votes
0answers
31 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 ...
0
votes
1answer
99 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 ...
0
votes
0answers
137 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 ...
0
votes
0answers
80 views

Mysql profiler different duration information_schema.profiling

Hi! I have a problem and no idea how to resolve it. I'm trying to use mysql profiler. I'm using the command: set profiling=1; to turn it on. Later, i execute my query which is correlated ...
1
vote
2answers
2k 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
0answers
40 views

Unique database presentation ideas

I have completed the first phase of a fairly large database schema design. I built the ER-diagram in DIA. I have to present the schema to an audience of fellow software engineers. Any thoughts on ...
1
vote
0answers
53 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
1answer
124 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
32 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: ...
3
votes
1answer
1k views

Drop auto increment hack w/o alter table?

I was looking around for a quick way to remove an auto increment from the definition of a primary key. As best I can tell the only way to do it is w/ an alter table or dumping all the data into a new ...
8
votes
4answers
852 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 ...
3
votes
2answers
215 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 ...