Questions tagged [schema]

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).

Filter by
Sorted by
Tagged with
3
votes
1answer
679 views

Is it possible to backup a subset of a database in Microsoft SQL Server?

Using Microsoft SQL Server, is there a way to backup a subset of a production database? I only want the database structure, and a very small amount of transaction data for a few selected tables. I ...
-2
votes
2answers
102 views

What's the best way to represent an event from a system in a chat?

Conversations -------------- id Messages -------------- id conversation_id user_id reply_to_id content created_at I was looking at this question and I had a question: How to build a table for a ...
2
votes
4answers
3k views

Handling currencies in a database

How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies? ...
0
votes
1answer
269 views

How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

Follow-up question from: Have postgres' pg_dump export an index Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a ...
3
votes
1answer
789 views

Have postgres' pg_dump export an index

I have an index that is very expensive to calculate, and I'm looking for ways to have it be restored or transfered in a simple copy fashion. It would be done along with the underlying data of course. ...
5
votes
6answers
2k views

Storing personal data on an invoice to preserve data integrity

I want to store invoice data like addresses of people. But the problem is addresses can change over time - for example someone will move out and change his address. The problem of storing such data ...
2
votes
1answer
4k views

Django postgres multiple schema

I'm trying to develop a django interface for an existing postgresql db, the db makes use of various schemas, looking at the literature the following example should work, but it only returns the schema ...
2
votes
1answer
61 views

How to export base and discover its structure

We are with a small problem at work. Our DBA decided to go around the world on a journey of self-discovery. I really love the idea and wish the best for him. The only detail is that he left without ...
1
vote
1answer
67 views

Database Schema Access

I have a database with multiple (10) schemas. I need a user to have access to two of them. If I grant access to the database and then GRANT SELECT to 2 schemas, is access to the other schemas DENY-ed ...
0
votes
0answers
27 views

Rename a schema that contains an extension used by another schema

How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)? I'm trying to temporarily rename a schema "dest" to "public" in ...
1
vote
0answers
297 views

What is a good schema for a database where users can search for inventory availability on certain dates (such as Airbnb)?

I apologize if this is not the right venue to ask such a question. This is my first time on this site and I'm open to suggestions. I am trying to find a good schema for a database that needs to ...
0
votes
1answer
31 views

Relations and Sets

From my understanding, One = {1,2,3,4,5...20}, and then if Two = {1}, then One won't map into Two. On that token, how come the answer is d instead of a?
3
votes
1answer
1k views

Database slows down at millions of rows, how to optimize schema and queries?

I am building a database to store Ethereum Transactions that are connected to specific addresses. Once the database table eth_addresses_tx starts hitting 3 million + rows, things start slowing down a ...
1
vote
0answers
157 views

How to design databse for storing object states in mysql?

I have multiple objects with multiple states. I want to build a timeline for each object. For example: an object might have a created, submitted, processing and success state. I would like to store ...
2
votes
2answers
128 views

Are the any inherent shortcomings in this schema?

This is derived from the SQL generated by sqldbm.com that I simplified because it wasn't generating foreign keys correctly and it seems to me that there's no need to pull in every foreign key from the ...
1
vote
2answers
3k views

Manually installing sample schemas in Oracle 11g

I am trying to manually install HR schema in my oracle environment by following the steps specified by oracle Help Center (11.2). Web link: https://docs.oracle.com/cd/E11882_01/server.112/e10831/...
2
votes
1answer
164 views

Creating a lost and found schema

I have a school assignment to create a lost and found schema. An admin can log a found item (someone turned it into the lost and found office) A user can report a lost item A user can claim a found ...
0
votes
1answer
51 views

Database schema for large possible product combinations and associated price [closed]

I'm new to database schemas and need some help. I have to draw up a database schema from a large, (2601) possible product combination, with width and height and associated price. Is creating ...
1
vote
0answers
41 views

Designing a database for the academic activities of individuals or departments

I have a database which stores the academic activities in which some individuals or departments are involved. An activity could be any among some of these types: article (written) course (imparted) ...
1
vote
1answer
193 views

Circular references for tracking currently active entity

I want to store active entity in one of my tables let's say I have 2 tables: Pirate Ship Named it like that to keep things interesting I want each pirate to own many ships but have only one active ...
1
vote
2answers
153 views

Designing a database for Questions, Options and Answers

A practice in trying to adhere to relational database techniques. Can anyone give me a little bump as to what I'm doing wrong/how this should be? In Short All Questions have 5 Possible Answers. That'...
0
votes
2answers
107 views

How can I combine these tables?

I have my system working with drinks, and now I'm ready to add burgers and other types of items. Right now I have 1 orders table with separate item and itemorder tables for each type of item. This ...
-1
votes
2answers
663 views

How to virtually update parent table when child table gets updated

I have parent table let say CAR, and its multiple props/records in child table let say CAR_PROPS. Now CAR has modified_date column like `modified_date` timestamp NOT NULL DEFAULT ...
2
votes
1answer
310 views

Optimizing Slow Changing Dimension (SCD2) joins

Right now I'm working on developing a DB schema for an application which requires versioning several different sets of numerical data which must be joined together for use in views. The application ...
2
votes
1answer
245 views

Database Schema -Images in separate tables?

I'm pretty new to web development and i'm creating a local guide app. Currently, I have the following table in the database: Organization Table Organization_Id(PK) name description site category_id ...
0
votes
1answer
479 views

Custom unit of measurement conversion problem

Let's say I have something that represents an item, let's say Beer. I store it in a table "Items". I also have a table with units of measurements. Like box, as beers can be sold in boxes. Now the ...
3
votes
1answer
285 views

Modelling the structure of a database involving products with multiple barcodes

What would be the best way to store barcodes for products? I have a situation where the same product can have many different barcodes, let's say I have 1 product and this product can have 100 ...
1
vote
1answer
45 views

How to make Event (like a Party) Sources mutually exclusive?

I'm designing an entity-relationship diagram (ERD) for a marketplace of events. An event can be created by a user, in which case the Event table must have owner_id or we pull it from an API in which ...
1
vote
2answers
414 views

Composite vs auto incrementing integer as Primary key

I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists. I have 3 tables, one for activities where it has an auto ...
1
vote
0answers
198 views

Modelling a scenario involving art exhibitions

Saffron Gallery is about to host an exhibition next month. A number of works of art (paintings, sculptures, etc.) will be exhibited. Business rules Every art work will have a barcode, a title and a ...
-2
votes
1answer
181 views

Which scenario fits for multiple payment method/gateway?

I have a web app that will support multiple payment method/gateway such as pay via PayPal, Credit Card, Debit Card, and more. Which scenario that probably fit for this case ? 1 Scenario I only ...
2
votes
2answers
41 views

Reason for having additional “type” table of all possible string values?

I've been looking over some sample schemas, and here's something I've seen done several times, but being new to MySQL I'm not sure why. Say I've got a table for items: CREATE TABLE item ( itemID ...
2
votes
2answers
2k views

Granting a user read-only access on [dbo] but full access on their own schema

I created a user at the database level. I created a schema [jm] for them to use. I would like them to be able to create whatever database objects only within their own schema - but limit their ...
2
votes
1answer
232 views

Is my DB design alright or not?

Quick dscription of a problem first of all I am still learning so please be understanding. Recently I've come across a post where someone was proposing nice schema for storing recurring events. I ...
1
vote
0answers
922 views

RDBMS sports league database

I am trying to create a database structure that will allow me to store records of league matches across multiple seasons and divisions. I have the following rules that need to be maintained: A league ...
3
votes
1answer
2k views

Default Schema allows dbo table selection without using schema prefix

I have created a Login and a database user on my server as follows: USE master GO CREATE LOGIN MyLogin WITH PASSWORD=N'Password123!' GO USE AdventureWorks2014 GO CREATE USER MyLogin FOR LOGIN MyLogin ...
2
votes
1answer
263 views

Physical vs Logical data independence

I am new to information systems and was working on a homework. I am not sure of the difference between physical and logical data independence. What is meant by physical and what is meant by logical ? ...
0
votes
1answer
30 views

Create a check constraint across multiple schemas in PostGIS [closed]

I'm a little new to this so apologies up front if my vocabulary is incorrect. I'm trying to create a check constraint that determines whether a new point is located within an existing polygon. The ...
0
votes
1answer
159 views

Checking if a transitive key dependency is in 3rd normal form

I'm currently trouble determining if one of my schemas is in third normal form. I have a table with columns image_id (A), upload_time (B), uploader_id (C), and image_file (D). A is a key (unique ...
1
vote
1answer
289 views

Organizing a comments table: How to justify the choice?

Business domain description Some of the entity types of interest with respect to the relevant business domain are money, user and progress. The users may need to post comments about money and ...
0
votes
1answer
15 views

Optimal table design for prizes that can be int or str

I'm storing data about prizes won by competitors during rounds of various events. Although most prizes are cash, non-cash prizes are awarded too. A common query will be summing a participant's ...
1
vote
1answer
325 views

Best Practice to Normalize Unoptimized SKU Table with 27 Columns (20 Alike) Columns

What a learning experience this has been. Started out not knowing a damn about SQL normalization or database techniques in general. As time has passed I'm beginning to see the pitfalls of trying to ...
1
vote
0answers
717 views

Database for personal income/expense, property and small business management

I am currently working on a personal finance management project. In my project a person want to manage his personal income, expense. Beside this, he has land business. He buy and sell lands. and some ...
0
votes
1answer
23 views

Help with schema design [duplicate]

I am having trouble designing a schema in which there are two models an employee and projects. I am storing employees details in a table structure as: id | employee | report_to wherein employee ...
1
vote
1answer
834 views

DB Schema for online market

I'm just search for advice. I have . Every Product type has its own set of characteristics. The Big problem is when someone add new Product type, aslo need create new table with product ...
1
vote
1answer
106 views

Lookup Table Decisions - Lookup Based On Characters In Specific Positions

(I do not know of a better wording for the title, but I am open to any edits on that) Disclaimer: not in anyway close to being a DBA, but I have at least basic knowledge. I am a software developer ...
1
vote
1answer
140 views

What would be an appropriate schema for this real life scenario?

A Company C provides an enterprise product to organisations (O1, O2, O3... On) and their sub-organisations (SO1 ... SOn). The product offers the organisations a database of content which is created by ...
1
vote
1answer
35 views

How will you map the permission control between two entities?

I have the following entities: Producer, Consumer. A Producer produces Food for the Consumer. A Consumer consumes Food produced by the Producer. Now I am tasked to design a schema which maps ...
0
votes
1answer
831 views

Cannot find the Order Entry folder in $ORACLE_HOME/demo/schema/

I have downloaded and installed the most recent version of Oracle 12c (12.2.0.1.0). As stated on the page https://docs.oracle.com/database/121/COMSC/installation.htm#COMSC00005, I expected to find ...
-1
votes
1answer
52 views

SQL Server : refactor schema to avoid repetition

I have an enormous prod SQL Server database : total size = 1.5 TB full backup duration = 4 hrs ( online ) that needs to be slimmed-down because it's causing a variety of issues for database ...

1 2 3
4
5
18