Questions tagged [table]
A data structure that organizes information into rows and columns.
529
questions
-2
votes
0
answers
27
views
UPDATE QUERY ACROSS TWO TABLES JOINED BY ANOTHER MSSQL
I have two tables. table1 I would like to use to update table2, i.e. table1.displayvalue to table2.fields. I would like it to match the rows.
They are joined like this:
table1.sectionid = table3....
- 1
0
votes
0
answers
18
views
Dynamically selecting a table in a query based on result from joined SELECT
I have seen many examples but they don't match with my special case (I know... everyone's case is a special case :D )
I have to match values from different tables, some of which are named after the id ...
- 101
0
votes
1
answer
84
views
Finding when columns are added- SQL Server
In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
0
votes
0
answers
21
views
Help with design using ltree on PostgreSQL
Currently designing a database to register animal species (Sharks and relatives)
1 table will hold species (name, attributes, and relative information)
I will have the taxonomy hierarchy for each ...
- 101
2
votes
1
answer
42
views
Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks
I have the following database schema (MySQL 5.7):
I have assistants that may be linked to either:
one customer (might evolve to multiple customers in the future, hence the association table)
one or ...
- 23
0
votes
1
answer
19
views
Flyway Migrate - External Table Create Throwing ORA-00922: missing or invalid option
We use flyway in our company to make changes to the various regions. The guy who set all this up is unfortunately left the company so I don't have anyone really to run this past.
I have a bit of SQL ...
- 11
0
votes
2
answers
47
views
transparently splitting a single table so the program doesn't notice
I am using homeassistant with mariadb. This is hosted on a Pi 4 with 8gb of RAM
I have been collecting data a long time, and a single table grows to 20gb. The storage is a M.2 NVMe via USB 3.
CPU ...
- 141
0
votes
0
answers
35
views
Drawbacks of using a single table to hold all relations
Say I have a simple user table that has some unique columns like id and email,
and a "level" column that defines the level of the user, such as admin, or moderator. Level is a fixed set of ...
- 165
1
vote
1
answer
62
views
Column containing keys from columns of another table
I have two csv files, countries and birth_rate_stat with the following data:
ISO
ISO3
ISO_CODE
FIPS
Display Name
Currency Name
Phone_Code
Region Code
AF
AFG
4
AF
Afghanistan
Afghani
93
Asia
AX
ALA
...
- 11
1
vote
1
answer
22
views
Table Schema problem, column with multiple dependencies
I have a product table shown below:
CREATE TABLE(
Id_Product INT PRIMARY KEY,
Product_SKU INT NOT NULL UNIQUE,
Product_Name nvarchar(100) NOT NULL,
Product_Section ...
- 11
3
votes
1
answer
69
views
Selecting second highest value using two SQL queries returns different results
I have the following tables from which I am to select the second highest salary:
Table 1:
id
salary
1
100
2
200
3
300
Table 2:
id
salary
1
100
I wrote two SQL queries to achieve this:
Query 1:
...
- 133
1
vote
0
answers
75
views
Auto Increment ID based on 2 columns
I have table called Logs. it has 4 columns ID, USER_ID, USER_TYPE, USER_ACTION
where
USER_ID is unique
USER_TYPE is unique for every USER_ID and
USER_ACTION can be anything 1,2,3 or A to Z.
USER_ID ...
- 11
0
votes
1
answer
60
views
table inheritance: TPH, TPT and single table vs taxonomies
according to this post and many others web resources, you can do table inheritance with Table Per Hierarchy Inheritance, Table Per Type Inheritance or Single table (single table for all sub classes).
...
- 129
0
votes
0
answers
14
views
GIS database table setup with multiple columns or FK
I am trying to design a environmental GIS table setup in a geospatial database. The workflow process is to create
a Permit To Disturb (PTD Table)
and for each PTD there will be a point, line and ...
0
votes
0
answers
59
views
Move data from existing column to new columns in Sequel pro
I need help with my SQL table, so thank you in advance for any comments. My table in Sequel Pro (example):
Object
Time
Intensity
. A.
. 1.
. 100.
. A.
. 2.
. 150.
. A.
. 3.
. 300.
. B.
....
- 1
0
votes
1
answer
67
views
unable to add data to a table in mysql, Error Code: 1054
##Created table using the following line:
create table sales (
product_id int,
sell_price float,
quantity int,
state varchar(20) );
##Tried to input values using the following line
insert ...
0
votes
2
answers
76
views
Separate tables for logging?
I would like to log new products, updates to products and deletions of products (and the same for other schemata [customers, locations etc]).
Would it be best to have a separate table (e.g. ProductLog,...
- 111
0
votes
1
answer
41
views
create table based on another table
I have a table called " Product " it has: ID_P, Name, expiration date
Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
- 41
0
votes
2
answers
139
views
Exec An SQL query in a table in Database 'B' from database 'A'
i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a ...
- 89
1
vote
2
answers
97
views
Help - Database design
We are implementing the new requirement in our current web application. If we discuss the database design for the new requirement, it appears to be more complex.
Requirement -
We have two webforms; ...
- 165
1
vote
1
answer
66
views
SQL Server find records that have the same data and keep one
I have a table containing Services (service_id) for different Packages(package_id)
I am trying to find Packages with the exact same Services and keep only one Package of them
dummy_id package_id ...
- 511
0
votes
0
answers
58
views
How do I get a big-picture feel for my database?
Background: I was just granted access to my company's SQL Server (MS SQL 2019 via Azure Data Studio). As I have not yet been assigned any specific projects I want start digging.
I've asked my DBA to ...
2
votes
1
answer
44
views
How to combine three tables for a queue in each Store in each town?
Sorry if my question is no appropriate for DA.
I have a State-city table with unique id for every town/city name. (id: town --> city --> State)
I also have a list of Store names associated to ...
- 131
3
votes
1
answer
395
views
Returning table name and schema name along with column values
I have table in SQL:
public.employee
name
age
abc
20
xyz
30
I want a query which would return the column values along with schema name and table name.
Input:
select * from public.employee
Output:
...
- 31
1
vote
0
answers
38
views
Table for an Appointment App
experts,
I am building out an appointment scheduler application. I am seeking advice on how best to tackle this issue.
I am building out my ERD and I am the point where I will be creating a ...
0
votes
2
answers
276
views
How to use foreign key, to remove subentries?
I try to delete all the subcategories and entries within this subentries, when i delete the main categorie from within one table. If i had multiple tables, i would use a foreign key with ON DELETE ...
- 13
0
votes
1
answer
489
views
how to run trigger on table on postgresql except one column
I'm using postgresql 13/14.
lets say I have a table with column A,B,C,D,E
I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for ...
- 1
0
votes
0
answers
21
views
P&T: which approach to take with app constantly creating new tables
A customer is complaining about low performance.
No cpu or memory bottlenecks observed on the system.
The problem is that their app is constantly creating new tables that are used for a while, then ...
- 9
0
votes
0
answers
48
views
SQL: How to do column operation on existing tables in database and create new table from it?
I am fairly new to SQL so may not be using correct terminology for my question.
I have got test data in database identified by part number in a "partNo" column.
Each row of the "...
- 1
0
votes
1
answer
19
views
Is it valid design to relate different but similarly structured data to resources using composite keys?
I have been taking an approach on small applications for some time now where I have a very similar data structure that might apply to more than one different type of resource. To avoid making ...
- 1
0
votes
1
answer
1k
views
SQL Summing a column value based conditional on another table's value
I'm working on a SQL query that returns a SUM of a value in a column/table based off a related value in another table's column being met. I'm getting an error:
The multi-part identifier "Charges....
user212215
0
votes
1
answer
533
views
DEFERRABLE not working as expected in Postgres
My app has the requirement that every table has a "created_by" column that references to the id column of user table. Even the user table. That means the created_by column would be a ...
- 165
0
votes
1
answer
52
views
How do I sync MySQL DB table
I have 9 MySQL servers in different environments. One of them is in the testing server. That one will be the first server if I have to make the change.
Is there any way I can sync the table from the ...
- 111
-3
votes
1
answer
631
views
Retrieving range of values from a lookup table using SQL
I am using SQL Server. I have two tables Result and Grading. Using T-SQL select statement, from the lookup table (Grading),I want to fill Grade column in Result table with the knowledge of range of ...
1
vote
1
answer
332
views
Flatten a JSONB file - What function/query
In a bit of a pickle at work - I have a Jsonb which I need to flatten a file that has similar structure:
{
"Manufacturers":[
{
"manufacturer":"Tesla",
...
- 25
0
votes
0
answers
35
views
Keeping a table up to date that has been created using a DB Query
I have created a new table view for customers, so that when they log in, they only see the information that they need to see...
I used the Query:
CREATE TABLE IF NOT EXISTS Customer( SELECT ColumnA, ...
- 167
2
votes
1
answer
143
views
Getting all shared values of two or more given values in one table (SQL)
I have a chat database where each user belongs to a chat; this relation is represented in the table "is_member(uid, cid)". Now, when creating a new chat, I want to check if there is not ...
- 23
1
vote
1
answer
66
views
Data modeling for employees with manager's record updated weekly
I would like a best practice or some direction on how to handle employee data, as I just took ownership of the database management
The table employee looks like this
id
employee
manager
week
score
1
...
- 111
2
votes
1
answer
535
views
Mysql - Table structure to hold history log without any primary key
I have mysql table that holds log data for some products, this data are deleted and inserted by a service every 5 minutes.
This is the table:
| id | product_id | dev_stage_id | production_order | log |...
- 121
-3
votes
1
answer
962
views
How to convert data separated pipe to rows?
I work on sql server 2017 I have table name #countriesData data as below
my issue here How to convert countries column data separated pipe to rows .
meaning every rev and company and year will have ...
- 103
0
votes
1
answer
2k
views
Grant All on All tabes in schema?
I have a question about grants...
I ran the following command, assuming it would allow me to make any changes to the tables in the public schema:
grant all on all tables in schema public to User1;
...
- 11
2
votes
3
answers
1k
views
Adding a column to a table if column not exists and fill the added column
I have a code that looks something like this
declare @query = '
Alter Table Temp
Add NewColumn int
update Temp
set NewColumn = 100'
exec (@query)
It will give error NewColumn does not ...
0
votes
0
answers
295
views
Large MySQL table has become unresponsive due to size, what can be done to rescue it?
I am currently building a custom project which collects large amounts of data, and I am now in a process of processing that data. One particular table, which holds all of the data that's been ...
- 101
21
votes
6
answers
3k
views
Is there a generic term for tables and views?
I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso.
As far as I understand, 'entity' is ...
- 321
1
vote
1
answer
669
views
Table design to store Refund Transaction information
I have a transaction table that contains purchases and refunds. I'd like to create a linkage between purchase and refunds in the same table (I decided not to use a separate table). I'm not sure which ...
- 1,294
1
vote
1
answer
930
views
DB strategies to store data in CSV files?
I'm new to the Postgres community and would love to hear your recommendations.
To give a bit more context, each file is associated with a unique zip code and address and contains 8,760 (365 days x 24 ...
- 13
0
votes
1
answer
250
views
Oracle - Big Table Archiving
Version: Oracle 12cR2 EE
The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2
Table size: 95GB
Table row count: 20M
Table partitioned? No
Indexes on table: there ...
- 29
-1
votes
1
answer
21
views
Table normalization of given table
I've got a table and I'm supposed to figure out why it breaks with 1NF, 2NF and/or 3NF.
I know the differences between these three, but I'm bad at telling what attributes are primary keys and what is ...
- 11
2
votes
1
answer
258
views
How would you design a table to store Configuration or a Setup in the following Questionnaire scenario?
I have a table named Questionnaire which will contain binary YES/NO type of questions with following columns.
Id INT
Question VARCHAR
GroupId INT
1
Are you a female?
1
2
Are you healthy?
1
3
Are ...
- 21
0
votes
1
answer
127
views
Help with Database Design for a CRM
I am creating a CRM for a call-center and I want to store the input field data in a database.
My problem is that I am quite new to this ordeal and have little - to no idea how to structure my database....
- 1