Database

Re: prior action plan to prevent sql database corruption

4-Sep-23 5:42

Kar_Malay wrote:
database corruption

The term "corruption" applies to the data that is stored by the database server and has nothing to do with valid/possible structuring of tables. Which your other two points would seem to suggest.

Preventing inconsistent data, orphans, etc is not something that anyone is going to cover in an online post. Buy yourself a database book or several (beginning and advanced) and read them before you start designing anything.

Kar_Malay2-Oct-23 9:22

Kar_Malay

2-Oct-23 9:22

yes, that's true
got good result by reconstructing database, eliminating unnecessary open connections, creating missing index, converting scan to seek, minimizing scan re-looping, distinct elimination / avoidant etc.
thanks

Fetch row and display in frontend column

Kesiena26-Aug-23 3:14

Kesiena

26-Aug-23 3:14

I have 5 tables in my db, I used one form to saved into 4 of them at once. I want the 5th one to be used as a master where I can store the foreign key and be used to display to a table in the frontend.
How do I make all of them add one ID?
How can I display a table row as a column in the frontend?

Re: Fetch row and display in frontend column

David Mujica28-Aug-23 3:16

David Mujica

28-Aug-23 3:16

Are you talking about fetching a column of data and displaying it as a single row? SQL Server has an interesting command that can do that.

STRING_AGG (Transact-SQL) - SQL Server | Microsoft Learn[^]

Re: Fetch row and display in frontend column

jschell29-Aug-23 5:01

Re: Fetch row and display in frontend column

29-Aug-23 5:01

How depends on the database.

But typically an identity column. The table would have something like the following which is for SQL Server. Other databases all have something similar. Mongodb uses a 'object id' (typically.)

{code}
something_id INT IDENTITY(1,1) PRIMARY KEY,
{code}

That is how you create the column. But populating it in the other tables is more complicated.
1. You must first insert into the main table to create the id.
2. You must then get that id. That almost always involves using a specific form of the insert (first part) which returns the id at the same time as the insert occurs.
3. You then use that return value to create (insert) into the other tables. Obviously you will need to add a column to hold that new value.

You can google for examples of using the above.

Rohan Saadat17-Nov-23 5:10

Rohan Saadat

17-Nov-23 5:10

It sounds like you have a scenario where you want to save data across multiple tables in a database using a single form. Additionally, you want to create a master table to store foreign keys and display the data in the frontend. Let's break down your requirements:

1. Generating a Common ID for Multiple Tables:
When you want to add data to multiple tables at once and have them share a common identifier, you typically use a primary key (ID) that is common across all related tables. This can be achieved through database design and relationships.

For example, let's say you have four tables: TableA, TableB, TableC, and TableD. Each of these tables has its own data, but they all share a common identifier, which could be a foreign key linking to a MasterTable.

Here's a simplified example:
CREATE TABLE MasterTable (
MasterID INT PRIMARY KEY,
-- Other columns as needed
);

CREATE TABLE TableA (
ID INT PRIMARY KEY,
MasterID INT,
-- Other columns for TableA
FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID)
);

-- Repeat the same structure for TableB, TableC, and TableD
When you insert data into MasterTable, you generate a unique MasterID and use it as a foreign key in the other tables. This way, you can maintain relationships between the tables.

2. Displaying Table Rows as Columns in the Frontend:
If you want to display data from a table row as columns in the frontend, you'll need to use SQL queries or your backend programming language to transform the data before sending it to the frontend.

For example, suppose you have a table named Data:
CREATE TABLE Data (
ID INT PRIMARY KEY,
MasterID INT,
ColumnName VARCHAR(50),
ColumnValue VARCHAR(50),
FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID)
);

This table stores data in a key-value pair format, where each row represents a piece of data related to a MasterID. To display this data with columns dynamically created based on the ColumnName values, you can use a pivot query.

Here's a simplified example in SQL:
SELECT
MasterID,
MAX(CASE WHEN ColumnName = 'Column1' THEN ColumnValue END) AS Column1,
MAX(CASE WHEN ColumnName = 'Column2' THEN ColumnValue END) AS Column2,
-- Add more columns as needed
FROM Data
GROUP BY MasterID;

This query transforms rows into columns based on the unique values in the ColumnName column.

Keep in mind that the specifics of these solutions might depend on the exact requirements of your application, the database system you're using, and the programming language/framework of your frontend.

Multi-user use the database at the same time

Siavash.BRY11-Aug-23 23:49

Siavash.BRY

11-Aug-23 23:49

When we have multiple users, What's the best way connect(logging, query on tables,...) to database?

Which is better and why?

1) Create users(DB User) in the SQL Server for each user(Windows App User).

2) Create a table which is fill with all App user and just connect to SQL Server with a user and query on that table.

Re: Multi-user use the database at the same time

Richard Deeming13-Aug-23 21:18

Richard Deeming

13-Aug-23 21:18

The only possible answer to that question is "it depends". There is no single "best" option; that's why there are different options available. Each option has both benefits and drawbacks, depending on your specific requirements.

For example, if you use SQL Authentication, then you have to store the SQL credentials somewhere on each client machine that needs to access the database. That runs the risk that a technically-minded user could find the credentials and connect directly to your database, bypassing the restrictions implemented in your code. You would have to deliberately design your database to restrict what the SQL user could do in order to mitigate this.

On the other hand, if you're using a web application / api to access your database, then that will typically run as a highly restricted local user on the web server. Setting that up to use Windows authentication is more effort than using SQL authentication. And since all requests would effectively be running as the same local user, you wouldn't be able to use the authenticated user to restrict access to the data.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Multi-user use the database at the same time

Dave Kreskowiak14-Aug-23 5:12

Dave Kreskowiak

14-Aug-23 5:12

There is no such thing as "the best option". There is only what is the most appropriate given the requirements for the application, the application type, and the environment it's running in, which we know nothing about.

There are far more options than just the two you listed.

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

Re: Multi-user use the database at the same time

jschell14-Aug-23 6:37

14-Aug-23 6:37

I am going to guess you have an application. Users (plural) use the application not the database.

The application, not the users, use the database. So there is only one user which is only visible to the application. The reason for this is because attempting to manage users both at the database and application level rapidly becomes a problem and provides no benefit.

The application itself, should provide a mechanism to validate each user. Then you can do things like log actions, in the application, for each user in the database (a table for that.) The application will use the database to implement this but it does not have anything to do with database users.

Tough one

Richard Andrew x6431-Jul-23 4:59

31-Jul-23 4:59

I can't figure out the following:

I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table.

I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders.

I have tried:

SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1

But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong.

Any help will be tremendously appreciated. Thank you.

The difficult we do right away...
...the impossible takes slightly longer.

modified 31-Jul-23 11:18am.

Re: Tough one

Richard Deeming31-Jul-23 5:33

Richard Deeming

31-Jul-23 5:33

How about:

SQL

SELECT UPC, COUNT(DISTINCT OrderHeaderId)
FROM OrderDetail
GROUP BY UPC
HAVING COUNT(DISTINCT OrderHeaderId) > 1

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Tough one

Richard Andrew x6431-Jul-23 5:35

Why do some websites provide Demo for their premium html themes while the codes can be seen using inspection tool in chrome?

31-Jul-23 5:35

Brilliant! I guess I was overthinking it.

Thanks, Richard. You saved the last few hairs I have left.

The difficult we do right away...
...the impossible takes slightly longer.

Alex Dunlop11-Jul-23 6:30

Alex Dunlop

11-Jul-23 6:30

There are some website that provide Demos for their premium Html/css/javascript themes while we can see and copy the source code without paying anything. Why?
Why don't they only provide a snapshot of the website theme to prevent any illegal copy?

Re: Why do some websites provide Demo for their premium html themes while the codes can be seen using inspection tool in chrome?

PIEBALDconsult11-Jul-23 6:01

PIEBALDconsult

11-Jul-23 6:01

It's a choice.

ORDER BY in UNION

_Flaviu29-May-23 5:14

29-May-23 5:14

Is there possible to get an UNION in such a way that second part of UNION to be ordered ?

I have:

SQL

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2 ORDER BY 3

The select from table1 will always get one row, and I need to order just records that come from table2, which could be more than one row ... it is possible to achieve that by SQL ?

P.S. I am using SQL Server.

modified 29-May-23 13:54pm.

jschell29-May-23 6:15

29-May-23 6:15

_Flaviu wrote:
it is possible to achieve that by SQL ?

No.

There are however solutions using derived languages such as TSQL or PL/SQL. You would need to specify which database you are using however for any consideration of that.

_Flaviu29-May-23 7:54

29-May-23 7:54

SQL Server (from Microsoft)

Richard Deeming29-May-23 21:37

Richard Deeming

29-May-23 21:37

The ORDER BY clause applies to the entire results; you can't make it only apply to one part of a UNION.

However, you could add an additional column to indicate which part of the UNION the row belongs to, and add that to your ORDER BY statement:

SQL

SELECT column_name(s), 0 As QueryPart FROM table1

UNION ALL

SELECT column_name(s), 1 As QueryPart FROM table2

ORDER BY QueryPart, SomeOtherColumn

If you don't want the additional column to be included in your results, you can use a subquery or CTE to hide it:

SQL

WITH cteUnion As
(
    SELECT column_names(s), 0 As QueryPart FROM table1
    
    UNION ALL
    
    SELECT column_name(s), 1 As QueryPart FROM table2
)
SELECT
    column_name(s)
FROM
    cteUnion
ORDER BY
    QueryPart,
    SomeOtherColumn
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Richard Andrew x6431-May-23 2:41

31-May-23 2:41

How about:

SQL

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM
    (SELECT column_name(s) FROM table2 ORDER BY 3)

The difficult we do right away...
...the impossible takes slightly longer.

_Flaviu13-Jun-23 8:28

13-Jun-23 8:28

I have tried:

SQL

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT b.id, b.name FROM
    (SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416)

But it doesn't like it:

SQL

SQL
Executing SQL directly; no cursor.
Incorrect syntax near ')'.
Statement(s) could not be prepared.

Did I understand correctly your thought?

Richard Andrew x6413-Jun-23 13:19

13-Jun-23 13:19

I'm sorry, try this slight modification:

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
    (SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S

The difficult we do right away...
...the impossible takes slightly longer.

_Flaviu14-Jun-23 9:29

14-Jun-23 9:29

Yes, that's worked:

SQL

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
    (SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S

but soon as I put ORDER BY:

SQL

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
    (SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416 ORDER BY 2) AS S

Error:

Terminal

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Richard Andrew x6414-Jun-23 13:00