Phil

right. but if the table is already created with all the custom fields it needs, it would only need to do standard select/insert/update/deletes

so if my installer knows what custom fields to create, i can do all that upfront and not worry about schema permissions

well, i guess some may use blobs or json, but EAV is much more popular (for obvious reasons)

from all the searching i've done, EAV is the only way people do custom fields

how do DBAs in larger orgs prefer to have their databases created? would the developer/vendor hand them an SQL script every time, or would they allow a compiled program to create the tables?

Amen on "all your options are bad"...

@psr I smell what you're steppin in. I'll consider that, thanks.

word. i'd still like to try my idea, though on a personal project or something. definitely not ready for DBAs in enterprises

@MichaelT thanks for letting me pick your brain a bit.

it's good to see a real-world example. still looks hairy, but i don't think i have much of a choice.

oy

yeah

but it sounds like the safest option

true. the EAV still doesn't sound like a great option... i may have a lot of custom attributes for a lot of things...

i wonder if it would be reasonable for an admin to create the database with software i give them, and then the actual web application wouldn't touch the schema after that

the clients i'm targeting will probably be in a bigger, more formal IT environment than i currently am

hm. that actually sounds like the biggest problem so far

interesting

so i'd have an ICreateTableCommand interface or something like that, and then the MSSQLCreateTableCommand and PostgresCreateTableCommand, etc.

i was also thinking that since i've abandoned the ORM, it might be relatively easy for me to create the necessary DDL for the different vendors and just use abstraction there

that's nice. i imagine it would be hard to query by json objects though

and that problem really isn't avoided by the EAV model either

i'll probably also have the type information in the config file, so while it's not type-safe, i can still work with it

however usually object.ToString() is sufficient

correct. another downside.

or i can get the column names from the config file

select * is an option

so i'd have a public IDictionary<string, object> GetAttributes() or something of the like

and i can manually control how the results of a query are materialized into model objects

which allows me to write my raw SQL

in particular i'm using dapper

ah. yeah one thing you correctly assume in your answer is that i've more-or-less abandoned the ORM

just one line in the attribute table maps directly to one thing in the thing table, and a simple join would give us all the custom info we need about that thing

the extra columns would just be extra information about the thing. there wouldn't be any foreign keys or constraints or anything

however i was also thinking that the extra columns could be set up in a configuration file by a power user, and we can remove much of that meta programming by referring to the config file

i don't question that part

right, yeah that's definitely a downside

ah yeah, sorry. i'm in IRC mode, not stackexchange mode

If your database is designed like this, where your attribute table only has one single column, i don't see upgrades being a problem simply because you'd never upgrade that table. Would you say that's a naive assumption on my part?

I see how a lot of your downsides are pretty big, though I wonder about a couple of them. For example, the clean upgrades bit and the users adding a column name that's a reserved word.

@MichaelT I have a question for your answer here: programmers.stackexchange.com/a/231943/7935

SL is dying unfortunately

yeah, i'm starting to come to the same conclusion

well if you trust what they say at justbeamit, it passes through their service but they don't "record" the file you're sending. still wouldn't trust it with anything private though

anyway... so no suggestions on a user-friendly FTP-ish upload button that runs on my own server?

eh, it's a step in the right direction

it's a pretty neat site if you know your friend is ready to download the file now

justbeamit.com

but if you have a 10 GB file you want to send, you gotta have your own always-on physically accessible server

at that point, it's no different than hosting your file on dropbox and posting a link to it in a standard email