C#•11mo ago

Am I doing this correctly? (Foreign Keys & Such, Entity Framework & SQL Server)

I'm trying to figure out how to store objects within objects in the database, that can only be accessed directly or through the UserModel... Kinda like having a sub-folder for organization sake so the usermodel doesn't have 30 columns in it. I only understand sql on a basic beginner level (one table for one thing, through entity framework only) and asking chatgpt has gotten me this far and i'm not srue if im even doing it right.

21 Replies

The Fog from Human Resources•11mo ago

"meow" and "rawr".

MetalkonOP•11mo ago

random words influenced by my cat on my desk lol

The Fog from Human Resources•11mo ago

You could Also add it as a JSON string into the db

MetalkonOP•11mo ago

The Fog from Human Resources•11mo ago

I think you can serialize whole objects in vanilla C# What database is that

MetalkonOP•11mo ago

sql server

The Fog from Human Resources•11mo ago

Ah Honestly I don't think you can easily store sub objects without creating all the values My approach for this would be to have the basic things and for things like object extensions I'll serialize them into a JSON object and store them in some additional attribute Also I'd say you should use the UNIX timestamp instead of ISO datetime, just preference tho since it's easier to perform math on it

MetalkonOP•11mo ago

is unix time generally the way to go in most apps compared to utc?

The Fog from Human Resources•11mo ago

the thing is that UNIX timestamp is the exact amount of seconds since 1970 (i think) so you can do very precise time measures and also doing things like subscriptions you can just have your subscription age in seconds and then compare if the current timestamp > (subscriptionStart + subscriptionAge) (this is just an example of the things you can do with it but its very useful to work with the UNIX format instead)

The Fog from Human Resources•11mo ago

https://tenor.com/view/cat-swimming-poopsie-silly-gif-14546589990767279660

Tenor

MetalkonOP•11mo ago

Btw I cannot modify the refresh token string through the usermodel like this api in the original code.

        [HttpGet("GetTest")]
        public async Task<ActionResult<string>> Get(int id)
        {
            UserModel user = await _db.Users.FirstAsync(x => x.Id == id);

            user.TestToken.Token = "meow mix cat food";
            await _db.SaveChangesAsync();

            return Ok(user.TestToken.Token);
        }

        [HttpGet("GetTest")]
        public async Task<ActionResult<string>> Get(int id)
        {
            UserModel user = await _db.Users.FirstAsync(x => x.Id == id);

            user.TestToken.Token = "meow mix cat food";
            await _db.SaveChangesAsync();

            return Ok(user.TestToken.Token);
        }

The Fog from Human Resources•11mo ago

i usually use stuff like MySQL and SQLite also i dont think you need to tell the async Task what to return i usually declare my functions as IActionResult and it works fine like this

[HttpGet("someResource")]
public async IActionResult(string someParameter) {return Ok();}

[HttpGet("someResource")]
public async IActionResult(string someParameter) {return Ok();}

Angius•11mo ago

This will work only because async here is useless as you're not awaiting anything Async methods have to return a task As for your initial question... it's not how I would've done it, but it should be fine

MetalkonOP•11mo ago

I do have some worries about using a json. While im using a test project here in this help thread (for learning purposes and testing how it works), my intention in the real project im working on is to save user game data, which includes player inventories which consist of lists of item objects and i'm worried about database performance, api performance of converting jsons every http request, or various string limits. These strings could be 4-5 digits worth of chars so idk if a json string is enough to handle it. I could probably impose inventory size limits (ex: 99 items) but the string could still be thousands of chars long. Was thinking maybe separate database tables would help with this.

The Fog from Human Resources•11mo ago

SQL should have types like TEXT and LONGTEXT, which can store 65.5k characters, or In the case of LONGTEXT it can store up to 4GB You could Also use a blob but I'm not sure if that's the kinda approach you'd want

MetalkonOP•11mo ago

no clue what a blob is 😄

The Fog from Human Resources•11mo ago

AFAIK it's just bytes in the db But I'm not sure I've never worked with them

Angius•11mo ago

Even SQLite supports dedicated JSON columns nowadays So if you want to store JSON in a relational db, use that

The Fog from Human Resources•11mo ago

Everything is varchar.

Angius•11mo ago

Except it's JSONB, binary JSON, so in case of Postgres at least you can query it and stuff IIRC you can even have FKs in the json

The Fog from Human Resources•11mo ago

Speaking of SQL columns, does it make any difference in performance / memory for the different types Like TEXT and LONGTEXT or INT and BIGINT

Gaming

Programming

Am I doing this correctly? (Foreign Keys & Such, Entity Framework & SQL Server)