Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
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.
theabbie278917dYour 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.
100110111144917dAlternative 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)
theabbie278917d@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.
F1973700917dNot a techie but you are solving a great problem for non-coders like me.
@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.
@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).
neeno203016d@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 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.
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.
IntrusionCM381016dAn 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.
... (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.