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)

1
vote
1answer
437 views

Understanding of schema and tablespaces - recommendation for a later seperation [closed]

DLDR: I would like to get a confirmation of my understanding of tablespaces and schemas (see image) a recommendation how to organize schemas and tablespace to make a later separation easy. ...
1
vote
1answer
1k 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
2answers
687 views

Is there a best practice for recurring calendar events

I'm not a dba but a php programmer, so that I'm not an SQL pro. Now, I have searched in both programmers and dba stackexchange sites but the answers are too specific or not conclusive. My question is ...
8
votes
1answer
583 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 ...
1
vote
1answer
230 views

Divide a PostgreSQL database into schemas

My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this ...
2
votes
3answers
131 views

What schema for different relation from two tables to another?

There are three tables instructors, meeting_points and bookings. Here are the relation I want: 1 instructor has 0, 1 or n meeting_points 1 booking has 1 meeting_point 1 meeting_point can be linked ...
1
vote
0answers
82 views

Alter schema of Access 2013 database with linked table

I'm trying to change the schema of an Access 2013 table. I want to lengthen some of the text fields. When I change the schema and try to save I get dialog with Operation is not supported for this type ...
0
votes
1answer
159 views

In regards to GS1 barcode - what size DB field are used?

In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection ...
12
votes
4answers
14k views

Is there a tool to check if my database is normalized to the third normal form?

I learned about normalization recently, and understand how important it is when implementing a new schema. How can I check if my database is 2NF or 3NF compliant ? Manual review is a sure option, ...
0
votes
1answer
434 views

Data Sync between servers with different schema in SQL Server [closed]

I have two servers 2008 R2 and 2005. The e-commerce site has been running with 2005 Server. I have migrated 2005 server data with modified data schema to 2008 R2 from yesterday's date of data. My ...
3
votes
2answers
2k views

Compare two MySQL dump files

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
2
votes
1answer
78 views

Database templates [duplicate]

Are there any site with database templates for specific purposes? (i.e. a database schema for users and authentication in a webpage) for different databases like Oracle, MySQL... I have been looking ...
2
votes
2answers
307 views

MySQL JOIN two tables and get latest result

I want to JOIN two tables and get the latest result from each one of those two in a single table. I'm kind of concerned about speed too since tables are growing kinda fast. Close to 60-70k records a ...
0
votes
0answers
107 views

pt-online-schema-change with statement based replication in MYSQL

Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR). What is the better way of doing it as currently I have ...
0
votes
1answer
94 views

Database design: handling time

I am developing a weekly timetable which ideally would allow teachers pick what subjects they teach. While most of this is fairly straightforward, from a conceptual point of view I am unsure how to ...
3
votes
0answers
47 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
0
votes
1answer
67 views

Generate Schema Only Script from SQL Server 2012 to SQL Server 2008R2

Hi this is the first time that I've encounter this error in generating script for my database, what do you think causes this error? Thanks. Please see image.
0
votes
1answer
69 views

Does MariaDB support schema changes inside a transaction?

MySQL doesn't support schema changes, inside a transaction so there's no way to roll back after during a failed migration involving schema changes. This capability is supported by Postgres and SQLite....
1
vote
2answers
880 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. so(...
0
votes
1answer
189 views

How could I improve my DB design?

I'm a second year software engineering major looking to create an iOS app that connects to a MySQL database (database not yet created) over the summer. I have a strong understanding of programming and ...
0
votes
0answers
56 views

Is doing an online alter on a MySQL table to add comments safe?

Is doing an online alter on a massive MySQL table to add or edit table or column 'comments' safe? Or do you need to take precautions like you would while adding, removing columns, etc on that table?
3
votes
3answers
160 views

Postgres: modify existing search_path (preserve current values)

Sometimes I would like to extend the existing search path, rather than replace it, say: To start with say it is already set like so: SET search_path TO schema_b, schema_c, public; I want to add my ...
2
votes
0answers
98 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
2
votes
1answer
439 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 ...
0
votes
3answers
119 views

Schema Design with “Seasonal” Data

I am developing a SaaS web application for School Management. What I need help is this: The whole application is purposed for the current year that the School is in. However, some data and ...
5
votes
3answers
2k views

Are there any tools to go from ERD to DB design and vice versa? [closed]

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

How can I limit a user to only be able to run procs in a schema, which access another db?

In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything ...
0
votes
1answer
63 views

Normilsation - 2NF and 3NF

I've been through several questions and youtube tutorials, i understand that 2NF is removing the partial dependencies and 3NF is the transitive ones, but i can't get my head around how the following ...
2
votes
1answer
256 views

Using the output from mysqldiff.exe to transform database2 to match database1

This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line. I have recently been trying to use mysqldiff.exe, part of MySQL ...
9
votes
3answers
3k views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
1
vote
1answer
228 views

db schema for a object with multiple foreign key tables

I have a question table which contains question_id, question_text and choice_type_id ( say freetext, binary choice and multiple choices ) Now, the question table (which has question_id, ...
1
vote
0answers
23 views

How to differentiate between class and attribute

I have this problem: Library has books, VDOs, CDs Journals. It gives loan to users. Books and Journals can be referential which cannot be loaned out more than 2hrs and non-referential which can be ...
2
votes
0answers
80 views

Tuple Relational Calculus help? [closed]

I have the following three questions in relation to the following diagram: 1) How many copies of the book titled “The Lost Tribe” are owned by the library branch whose name is "Sharpstown"? {b....
2
votes
1answer
51 views

Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column?

In similar spirit to How to implement a 'default' flag that can only be set on a single row I would like to specify that, for any given list of possible names for a business, only one of the ...
0
votes
1answer
869 views

Is an ER diagram an example of a conceptual, logical or physical schema?

I have been reading databases, and I clearly understand the difference between a conceptual, logical and physical schema. An ER diagram is an example of which of these schema's?
11
votes
6answers
18k 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 ...
0
votes
1answer
1k views

Select rows from table where each row refers to a different schema

I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by ...
3
votes
2answers
218 views

Is EAV an appropriate design for a collection of coexisting metadata-rich “fact estimates” on objects? [closed]

I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For ...
1
vote
0answers
29 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
0
votes
0answers
799 views

Railway Reservation System [Miniature] Schema

I am working on designing a schema to support the development of a miniature railway reservation system, using oracle 11g XE. This is for a project purpose, I am need to be sure about the correctness ...
1
vote
1answer
842 views

Use a set database schema or EAV design? [duplicate]

I have already created a database design with a set schema. The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders ...
5
votes
2answers
909 views

noSQL ACID and consistency for banking

We are building a real time banking platform and app. Our prime concern is safety and consistency of our data across multiple db servers. We need transactions and we can't afford to risk power ...
1
vote
0answers
56 views

Mongodb schema design validation

I am building an app which allows users to do some "tasks" to earn points. The "tasks" are part of a "campaign" which themselves are a part of "brands". I am confused about the schema for storing the ...
3
votes
1answer
484 views

How to efficiently create a new PRIMARY KEY CLUSTERED definition online

Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online. I would ...
0
votes
2answers
29 views

Can we determine FDs if we are given some tuples?

I am taking a databases course and i'm learning about functional dependencies. I have searched around and did not find any information on this. My questions is if we are given a table with some tuples,...
1
vote
1answer
485 views

Not able to solve issue: Incorrect string value: '\xC5\xABt\xC4\x97'

I am facing database error Incorrect string value: '\xC5\xABt\xC4\x97'. I did a lot search and tries a few ways, include move from utf8 to utf8mb4. But still not able to solve it. Following are the ...
2
votes
1answer
748 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
2answers
48 views

Is it a good idea to add a reserved field to a large MySQL table?

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible. Is it a good idea to add a text column to each table (say, meta) to save some ...
2
votes
1answer
856 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
260 views

Dropped connections, corrupted schema … is MySQL sick?

Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are: Not being able to forward engineer said ...