How would I store dynamic data in an SQL database?

Question

0.00/5 (No votes)

See more:

I have a game and website that I am working on and I am wondering what the best way to store certain amounts of dynamic data would be when it is loaded from a master table row.

What I have tried:

I have so far come up with 2 options.

1.) Create a "fields" table that has custom fields that foreign key out to a parent table row where in the API a query can be performed to get all of the created "Fields" to a specific data object (this would cause this table to be quite large though since I am not sure how many parent data tables will be created.)

2.) Store a scrubbed stringified JSON object under a single field called something like "ItemJSONData" within the parent database object, obviously though, security is a big concern here.

Any other suggestions would be quite welcome. Thanks!

Posted 10-Jul-24 7:43am

Baraiboapex

Updated 10-Jul-24 7:53am

v2

Add a Solution

Comments

RedDk 10-Jul-24 20:32pm

Perhaps https://www.codeproject.com/Articles/1030954/MVC-Web-API-and-AngularJS-For-Are-You-Genius-Game article holds some keys to finding answers in your quest.

PIEBALDconsult 10-Jul-24 22:35pm

Depending on the needs of performance, I'd consider an XML column.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2024-07-10T08:17:00

I have done this type of thing in the past. The latest evolution of this was to use a json structure in a payload table, a child table with a parent relationship. Effectively, the parent contains the key and the child table is the value.

Here's an example SQL Server schema you could use:

SQL

CREATE TABLE [project] (
	[id] bigint IDENTITY(1,1) NOT NULL UNIQUE,
	[key] nvarchar(256) NOT NULL UNIQUE,
	PRIMARY KEY ([id])
);

CREATE TABLE [payload] (
	[id] bigint IDENTITY(1,1) NOT NULL UNIQUE,
	[project_id] bigint NOT NULL,
	[payload] nvarchar(max) NOT NULL,
	PRIMARY KEY ([id])
);

ALTER TABLE [payload] ADD CONSTRAINT [payload_fk1] FOREIGN KEY ([project_id]) REFERENCES [project]([id]);

To be honest, I prefer to use Postgres nowadays, but I have drafted this as SQL Server to give you the general idea.

RickZeeland · Answer 2 · 2024-07-20T20:26:00

I agree with Pete that PostgreSQL is excellent and would recommend it too, but if your needs are simple LiteDB might be an option too, it has the advantage that it is very simple to use and it stores objects as JSON like structures, see:
Data Structure - LiteDB :: A .NET embedded NoSQL database[^]

How would I store dynamic data in an SQL database?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0