Questions tagged [database-schema]
A description of the structure of the database - including the structure of the tables, columns, constraints, views, etc, etc... that make up the database.Normally defined in the text, generally SQL in an RDBMS, the schema describes the complete blueprint for the database, defining everything about the database except that data itself.
2,946
questions
1377
votes
19
answers
1.2m
views
How can I list the tables in a SQLite database file that was opened with ATTACH?
What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the ATTACH command on the sqlite3 command line tool?
- 50.9k
1043
votes
20
answers
747k
views
How can I rollback a specific migration?
I have the migration file db\migrate\20100905201547_create_blocks.rb.
How can I specifically rollback that migration file?
- 110k
670
votes
16
answers
553k
views
Export schema without data
I'm using a MySql database with a Java program, now I want to give the program to somebody else.
How to export the MySQL database structure without the data in it, just the structure?
- 9,615
603
votes
14
answers
638k
views
How to see indexes for a database or table in MySQL?
How do I see if my database has any indexes on it?
How about for a specific table?
- 264k
501
votes
9
answers
262k
views
Is there an SQLite equivalent to MySQL's DESCRIBE [table]?
I'm just getting started learning SQLite. It would be nice to be able to see the details for a table, like MySQL's DESCRIBE [table]. PRAGMA table_info [table] isn't good enough, as it only has basic ...
- 28.6k
462
votes
5
answers
636k
views
How do I show the schema of a table in a MySQL database?
From the MySQL console, what command displays the schema of any given table?
- 44.7k
385
votes
10
answers
273k
views
Difference Between Schema / Database in MySQL
Is there a difference between a schema and a database in MySQL? In SQL Server, a database is a higher level container in relation to a schema.
I read that Create Schema and Create Database do ...
- 35.9k
368
votes
4
answers
301k
views
What are OLTP and OLAP. What is the difference between them?
Actually what do they mean? All articles I find about them don't give me an idea, or my knowledge is too insufficient to understand it.
Will some one give me some resources with which I can learn ...
- 5,171
330
votes
31
answers
299k
views
How to list table foreign keys
Is there a way using SQL to list all foreign keys for a given table? I know the table name / schema and I can plug that in.
- 11.3k
250
votes
11
answers
266k
views
How to get all columns' names for all the tables in MySQL?
Is there a fast way of getting all column names from all tables in MySQL, without having to list all the tables?
- 2,571
244
votes
11
answers
289k
views
How can I initialize a MySQL database with schema in a Docker container?
I am trying to create a container with a MySQL database and add a schema to these database.
My current Dockerfile is:
FROM mysql
MAINTAINER (me) <email>
# Copy the database schema to the /...
- 2,664
236
votes
10
answers
324k
views
Is it possible to specify the schema when connecting to postgres with JDBC?
Is it possible? Can i specify it on the connection URL? How to do that?
- 11.2k
216
votes
6
answers
158k
views
schema builder laravel migrations unique on two columns
How can I set a unique constraints on two columns?
class MyModel extends Migration {
public function up()
{
Schema::create('storage_trackers', function(Blueprint $table) {
$table->...
- 30.3k
211
votes
5
answers
245k
views
Difference between database and schema
What's the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data.
If a Schema is deleted, then are all the tables contained in that schema also deleted ...
- 8,854
205
votes
7
answers
589k
views
How to store arrays in MySQL?
I have two tables in MySQL. Table Person has the following columns:
id
name
fruits
The fruits column may hold null or an array of strings like ('apple', 'orange', 'banana'), or ('strawberry'), etc. ...
- 11.9k
181
votes
11
answers
371k
views
PostgreSQL: Export the schema of a database
My computer broke down but fortunately I backed up
the folder C:\Program Files\PostgreSQL.
Now I'm working in a new computer and I would like to import the previous
Postgres databases that are ...
- 4,661
180
votes
4
answers
69k
views
How to design a product table for many kinds of product where each product has many parameters
I do not have much experience in table design. My goal is to create one or more product tables that meet the requirements below:
Support many kinds of products (TV, Phone, PC, ...). Each kind of ...
- 16.2k
158
votes
10
answers
241k
views
How to fix Error: "Could not find schema information for the attribute/element" by creating schema
I have a windows forms application written in VS2010 with C# and get the following errors in the app.config file:
Message 4 Could not find schema information for the attribute 'name'
Message 8 ...
- 4,738
139
votes
2
answers
182k
views
When to use MyISAM and InnoDB? [duplicate]
MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than ...
- 1,560
114
votes
3
answers
65k
views
Good practices for designing monthly subscription system in database [closed]
I'd like to know how to design a monthly subscription software system in a database.
These systems are broadly used all over internet, though I can't find lots of stuff about database design.
In my ...
- 10.9k
108
votes
4
answers
56k
views
What is the purpose of the ConcurrencyStamp column in the AspNetUsers table in the new ASP.NET MVC 6 identity?
What is the purpose of the ConcurrencyStamp column in the AspNetUsers table in the new ASP.NET MVC 6 identity?
This is the database schema of the AspNetUsers table:
It is also there in the ...
- 16.7k
100
votes
5
answers
55k
views
mysqldump - Export structure only without autoincrement
I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. mysqldump --no-data would almost do the job, but it keeps the auto_increment ...
- 6,581
92
votes
1
answer
44k
views
Mysqldump: create column names for inserts when backing up
How do I instruct mysqldump to backup with column names in insert statements?
In my case I didn’t a normal back up with insert sql’s resulting in
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `...
- 16.1k
87
votes
16
answers
245k
views
How to find column names for all tables in all databases in SQL Server
I want to find all column names in all tables in all databases. Is there a query that can do that for me?
- 269k
79
votes
7
answers
32k
views
Using multiple PostgreSQL schemas with Rails models
I have a PostgreSQL database for my Rails application. In the schema named 'public' the main Rails models tables are stored etc. I have created a 'discogs' schema which will have tables with names ...
- 1,907
75
votes
4
answers
31k
views
Is it OK to update a production database with EF migrations?
According to this blog post most companies using EF Migrations are supposedly not updating the database schema of production databases with EF migrations. Instead the blog post's author recommends to ...
- 33.2k
72
votes
11
answers
73k
views
mysqldump - exclude some table data
Is it possible, using mysql dump to export the entire database structure, but exclude certain tables data from export.
Say the database has 200 tables, I wish to export the structure of all 200 ...
- 34.9k
66
votes
9
answers
32k
views
Should I use a single or multiple database setup for a multi-client application? [closed]
I am working on a PHP application that intends to ease company workflow and project management, let's say something like Basecamp and GoPlan.
I am not sure on what the best approach is, database-wise....
- 82.2k
58
votes
7
answers
68k
views
copy database structure without data in mysql (with empty tables)
Is there any way to copy database structure without data in MySQL, so the new database will be the same as it is copied from, but with empty tables.
After getting some suggestions I tried the command,...
- 2,876
55
votes
2
answers
156k
views
How do I set the default schema for a user in MySQL
Is there a way to set a default schema for each user in MySQL and if so how? Where user x would default to schema y and user z would default to schema a.
- 1,578
54
votes
4
answers
23k
views
Adding an one-out-of-two not null constraint in postgresql
If I have a table in Postgresql:
create table Education (
id integer references Profiles(id),
finished YearValue not null,
started YearValue,
...
- 551
53
votes
14
answers
300k
views
DB2 Query to retrieve all table names for a given schema
I'm just looking for a simple query to select all the table names for a given schema.
For example, our DB has over 100 tables and I need to find any table that contains the sub-string “CUR”. I can ...
- 2,791
52
votes
9
answers
46k
views
What's better - many small tables or one big table?
I've got a database which will store profiles about individuals. These individuals have about 50 possible fields.
Some are common things like, first name, last name, email, phone number.
Others are ...
- 11.6k
52
votes
4
answers
59k
views
Django and postgresql schemas
I've been trying to solve this one all week, help very much appreciated.
I have various schemas in a postgres db and I would like to be able to map to them from within the same or across different ...
- 1,933
51
votes
9
answers
151k
views
Differences between key, superkey, minimal superkey, candidate key and primary key
I'm new to MySQL, and I'm really confused about the different terms that I've encountered. I tried googling the answer but the results are really confusing and when I try and understand it just seems ...
- 1,989
50
votes
6
answers
54k
views
Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?
I'm trying to do this, but it seems like MySQL isn't allowing me. Is there a solution to this issue or am I expected to always include the function in my INSERT queries?
CREATE TABLE foo(
created ...
- 36.5k
47
votes
11
answers
88k
views
Dynamically changing schema in Entity Framework Core
UPD here is the way I solved the problem. Although it's likely to be not the best one, it worked for me.
I have an issue with working with EF Core. I want to separate data for different companies in ...
- 2,379
45
votes
5
answers
101k
views
How to change schema of all tables, views and stored procedures in MSSQL
Recently we were having issues on our database server and after long efforts it was decided to change the database server. So we managed to restore the database on another server, change the ...
- 17.3k
44
votes
4
answers
42k
views
What mysql database tables and relationships would support a Q&A survey with conditional questions? [closed]
I'm working on a fairly simple survey system right now. The database schema is going to be simple: a Survey table, in a one-to-many relation with Question table, which is in a one-to-many relation ...
- 86.4k
44
votes
8
answers
163k
views
SQL Query to search schema of all tables
I am working on a SQL Server 2008 Db that has many tables in it (around 200). Many of these tables contain a field by the name "CreatedDate". I am trying to identify all the table schema with this ...
- 13k
41
votes
6
answers
57k
views
Import and export schema in cassandra
How to import and export schema from Cassandra or Cassandra cqlsh prompt?
- 439
40
votes
2
answers
47k
views
Setting schema name for DbContext
I know how to set the schema for a table in my context but is there a way to set the default schema for all the tables in a context?
i.e.
[Schema = "Ordering"]
public class MyContext:DbContext
{
...
- 8,149
39
votes
7
answers
81k
views
How to duplicate schemas in PostgreSQL
I have a database with schema public and schema_A. I need to create a new schema schema_b with the same structure than schema_a.
I found the function below, the problem is that it does not copy the ...
- 4,110
39
votes
5
answers
15k
views
mongoengine - Ignore extra fields for schema validation
I am trying to query my database. Some records currently have extra fields that are not included in my model schema (by error, but I want to handle these cases). When I try to query the DB and ...
- 1,395
39
votes
2
answers
36k
views
Cannot create a new table after "DROP SCHEMA public"
Since I wanted to drop some tables and somebody suggested the below and I did it:
postgres=# drop schema public cascade;
DROP SCHEMA
postgres=# create schema public;
CREATE SCHEMA
Then I got problem ...
- 967
39
votes
4
answers
32k
views
MongoDB Structure for message app
I am breaking my mind up thinking about a good document structure for handling a message app.
I basically need three (or four) types of objects:
The user (username, email, password, etc.)
The ...
- 8,059
39
votes
4
answers
67k
views
MongoDB Diagram Design Tool [closed]
Planning a project using MongoDB and looking for a tool to map out the database.
Any suggestions of a database design tool similar to http://ondras.zarovi.cz/sql/demo/ that specifically supports ...
- 841
37
votes
6
answers
28k
views
Why use a 1-to-1 relationship in database design?
I am having a hard time trying to figure out when to use a 1-to-1 relationship in db design or if it is ever necessary.
If you can select only the columns you need in a query is there ever a point to ...
- 31.9k
35
votes
1
answer
22k
views
Migrating ManyToManyField to null true, blank true, isn't recognized
I have made a model change from
standard = models.ManyToManyField(Standard)
to
standard = models.ManyToManyField(Standard, blank=True, null=True)
South schemamigration for this app doesn't ...
- 378
35
votes
4
answers
17k
views
InfluxDB : single or multiple measurement
I'm a beginner with influxDB and after reading the Schema design documentation a question remain.
How to decide if you should use one measurement with multiple fields or multiple measurement with ...
- 14.8k