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

167 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
2k views

ERD for a web development company

I am working on an ERD which represents the relationships between entities in a Web development company behavioral model. There I have entities like: Customer Service ----------- (like: web ...
4
votes
0answers
70 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 ...
3
votes
1answer
43 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
3
votes
1answer
1k views

speed up PostgreSQL calculations via OLAP warehouse?

So I have asked these three questions: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 https://quant.stackexchange.com/questions/29572/building-financial-data-...
3
votes
1answer
56 views

When to create a new table if functionality is the same?

I am having trouble making database schema/design decisions. The database I am building is relational. Consider this example: I have these tables: orders, products, companies. I want to create ...
3
votes
0answers
361 views

Database Designs Alternative to Polymorphism in Establishing Relationships

I'm working on a database which handles replacement of parts in a machine. Rules A machine has many components A machine has a set of assembly instructions Many components have many part (screws, ...
3
votes
1answer
158 views

Schema for scarce goods

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I ...
2
votes
2answers
56 views

How to design a database for search history with multiple facets?

I want to show users a history of their searches, which can often be complicated: any of these terms: [a,b,c] all of these terms: [d,e,f] not these colors: [h,i,j] Since these searches are done ...
2
votes
1answer
57 views

Handling unique IDs on client side

Reading around on the topic hasn't got me closer to understanding best practices. I'm also relatively new and self-taught in programming, so might have some questions about how to actually execute the ...
2
votes
0answers
63 views

Surrogate foreign keys allow inconsistent data - which normal form does this violate?

We have inherited a system which has surrogate keys on every table (that is, an auto-increment primary key, id). All of the foreign key constraints are on individual surrogate keys, and this seems to ...
2
votes
0answers
50 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
2
votes
1answer
936 views

Inventory Database Design? Get historical data from DB for reporting & accounting

Following is the design of my db: Product: ID Name Quantity in Hand (as on date - total across all format) Reorder_point Remarks Warehouse: ID Name Address Remarks is_default Inventory: ID ...
2
votes
0answers
130 views

Database Table Schema Circular Reference issue

Trying to fix a Circular Reference in a schema created by a developer, but having a hard time. This is as far as I have gotten. The black line is the tables that are needed to create a file. The red ...
2
votes
0answers
538 views

Handling Strong and Weak entity types in a one-to-one relationship

I am modelling a database for a term project and I have come across something I do not have a definite answer to. Diagram I was supplied with the following Entity-Relationship diagram: Description So,...
2
votes
0answers
517 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender =...
2
votes
0answers
299 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
0answers
378 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....
2
votes
0answers
1k 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 ...
2
votes
0answers
2k views

which role-permission database design?

I want to manage roles and permissions. Most of designs on web look like this tables: Users Roles UserRoles Permissions RolePermissions Here, what is permissions? I am thinking for such a design ...
1
vote
0answers
20 views

Should I use Postgres' JSON columns for additional related data of different types and not yet known?

I have a simple database table, lets call it tbl_main with a few columns and relations to other tables via foreign keys. The tbl_main is the most frequently written to table, as in it receives new ...
1
vote
0answers
38 views

SQL Server Replication How to keep primary key schema at publisher

I have implemented Transactional replication and want to have similar schema structure at both subscriber and publisher end. But while creating the publication, Not For Replication appears in front of ...
1
vote
0answers
26 views

PgAdmin: backup and restore schemas using the GUI

I know how to backup and restore one or more schema(s) from a PostgreSQL database using psql: Go to the folder that contains the executables of PostgreSQL: cd C:\Program Files\PostgreSQL\11\bin ...
1
vote
0answers
29 views

How to structure SQL data model to maintain data integrity

Background I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu. I'm having trouble creating a SQL db schema that accurately represents the domain while ...
1
vote
0answers
24 views

Default Schema not being applied even after setting default schema and default DB

Hello Stack Exchange! I have an interesting problem! Let me set the stage for you: 1) Windows Authentication 2) Default Schema identityiq has been set for the user with login XCORP\USERA 3) ...
1
vote
0answers
30 views

Schema design for an entity with 'heterogeneous' status types

Consider a notification system which gets some event data and list of business email addresses as input (the list is basically computed by looking at which users/addresses are subscribed to which type ...
1
vote
0answers
119 views

Schema for social network using the supertype/subtype pattern

Scenario I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
1
vote
0answers
48 views

Schema design: Anonymous user's data across registration

This is a schema design question; implementation is going to be Mongo, but answers are platform agnostic. Given an app, which has users, sessions, and items. All visitors will have a session ...
1
vote
1answer
47 views

Table design - parent with always exactly 1 or 2 children

Lets say I have the following existing tables (both with a primary key column named Id): TableA TableB Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 ...
1
vote
0answers
61 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can have ...
1
vote
2answers
330 views

Copy a database to a new schema in an existing database

Just curious if there is a way to create a new schema that is based on an existing database. Assuming the existing database has .dbo schema only.
1
vote
0answers
54 views

Schema: Table with many rows vs single table with many columns

I'm torn between two different schemas and would love some of your thoughts. Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 ...
1
vote
2answers
145 views

postgresql 9.* - user cannot SELECT from VIEW inside a SCHEMA as DEFAULT PRIVILEGE

hope you can help me. I have 3 users First is me, the db admin. I run the GRANT queries, I can do anything. (name doesn't matter now) Second is the data manager user. = manager_u Third is the data ...
1
vote
0answers
26 views

What are the most effective schema migration strategies?

I recently came across a migration system that relies on incremental numbers, so the scripts are executed in ascending order. A folder containing a few scripts might look like: Script1.sql ... ...
1
vote
0answers
205 views

Restrict postgresql's public schema's tables visibility in tableua

My requirement is to display only one particular table or materialised view when user give db_host, db_port, db_name, db_username, and db_password to tableau. Currently tableau is showing all the ...
1
vote
0answers
496 views

Interesting Database Designs for Product Catalogue including a Recursive Approach

I am doing some work for a client and they are creating a database of products of which I need to consume product data when it's created and updated to store in a third party service and then present ...
1
vote
0answers
36 views

Checking if schema name is included in every sentence

I got some oracle database scripts for creating the whole database of an application, including sentences for creating tables, sequences, synonyms, inserts and updates of data, etc I need to ensure ...
1
vote
0answers
18 views

How to store a multi-lingual “dictionnary” in a NoSQL database?

I hope this is the good Stack to ask this question, pardon me if it's not. I need to categorize images and need manipulate a lot of words. I'll explain what's bugging me first and then there is a ...
1
vote
0answers
582 views

Gaming shop database schema

I'm trying to design schema for my personal project that would be an online gaming shop. I would like to ask you guys for any suggestions/tips on how could I maybe improve my current draft. Concerns: ...
1
vote
0answers
36 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...
1
vote
0answers
132 views

Schema changes in transactional replication

I have a publisher (SQL Server cluster) in one box and the distributor/subscriber together is on the other box. I have a requirement by the vendor that if there is a new build (schema/database ...
1
vote
1answer
114 views

How to handle a table schema with a new relationship subtype in an interviews scenario?

I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below ...
1
vote
0answers
306 views

schema for product prices based on membership level

I need to model the following scenario: Customers have 3 options of annual memberships: [basic:$0,silver:$50,gold:$100] We have a supplier with product A at a cost of $30 per unit. (We will have ...
1
vote
0answers
65 views

A flexible and robust schema for storing precomputed values/metrics

Background I'm dealing with a table that contains a lot of historical data. What I am doing is generating a bunch of metrics based on different combinations of column values and date periods (e.g. ...
1
vote
1answer
54 views

How to model a database for prices that vary depending on their categories and parameters?

My first post on DBA! Advanced apologies if I made any mistake. Before jumping into the schema and tables, I would like to share what I am trying to achieve first. I am working on sort of a courier ...
1
vote
0answers
207 views

How to create entity-relationship diagram for tables that can potentially be united by an “IS A” relationship?

I have this schema: create table hall( building-id int, hall-id int, capacity int, primary key(building-id, hall-id) ); create table semester( sem-id char(5), start-date date, eend-...
1
vote
1answer
18 views

Permission errors on insert after schema drop/create (Pg 9.0.23)

We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from ...
1
vote
0answers
341 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 ...
1
vote
0answers
177 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 ...
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
47 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 ...