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

155 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
0answers
34 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
997 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
356 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
146 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
52 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
0answers
58 views

A multiple choice question, with multiple or only one correct answer, and sometimes no correct answer

It's a question & answer based model, and here is the scenario: A question with multiple choices given, with 3 to 7 correct answers among those choices. A question with multiple choices given, ...
2
votes
1answer
56 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
61 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
358 views

User created automatically because of AD Group?

I have a Development SQL Server that I'm managing with Active Directory Groups, sometimes when I do my checks on the server I find that some users were "automatically" added to SQL Server Database ...
2
votes
0answers
125 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
503 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 ...
2
votes
0answers
497 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
295 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
371 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

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
28 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
85 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
37 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
50 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 ...
1
vote
2answers
168 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
51 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
60 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
173 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
406 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
16 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
469 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
119 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
90 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
259 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
62 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
45 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
190 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
295 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
156 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
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
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 ...
1
vote
0answers
920 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 ...
1
vote
0answers
713 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 ...