The ability for a customer to add arbitrary fields is pretty common for certain kinds of internal enterprise applications. There are several options to implement functionality like this, your example is probably the more unusual option. A typical implementation is the Entity Attribute Value model, which is probably what you mean by addding rows instead of ...
For each distinct source, write some code which normalizes data from this source. Insert the normalized data into the database.
Realize that this doesn't scale, and request that the sources provide data in a common format so you don't have to write a normalizer for each one.
Realize that your sources are not under your control and won't adapt to your needs. ...
Language Integrated Query (LINQ)
What is a query and what does it do?
A query is a set of instructions that describes what data to retrieve from a given data source (or sources) and what shape and organization the returned data should have. A query is distinct from the results that it produces.
LINQ is the answer to my question. LINQ allows developers to ...
The problem you describe is known as the "n+1 select problem" and is referenced in ORM software. It would help if you dug into this question https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping
You either need to do a join and fetch duplicate data or do multiple selects. There are tradeoffs in ...
You can issue a query that returns
customer_name | customer_id | item_id | item_name
bob | 0 | 0 | chair
bob | 0 | 2 | hat
steve | 1 | 2 | hat
steve | 1 | 1 | table
And then construct 2 Customer instances,...
The best way I know is, you can create an alias of that property at the beginning of the method or at initialization and whenever you make changes in your model, you will just have to make changes in that alias property.
SQL recordset as a HTML Table - a bad practice?
No. But I think you're really asking if editing is ok. I say it is ok.
I've done this with ASP (i.e. like "Star Wars" when there was only the one movie). The point was to have user-enterable and editable data. Here is what worked for me.
NEVER let the user create, edit, or delete keys.
Must keep key ...
It is very common to use a “status” column in tables even if it might add overhead.
The added benefit is you can do “logical or soft” deletes in such cases as you might use a “status” column as representing the status of your row data.
I (almost) always include some kind of “status” or “flag” column but that may due to the types of applications I have worked ...
If you have ordinary data, convert it to JSON, and store it in a database, that’s not clever. But if the same JSON can arrive from your server or can be sent to the server, and you need to persist it as well, then you store it in some persistent store, and that persistent store could be SQL of course. I’d expect that you have code that expects to be given ...
Storing JSON in an SQL database is a perfectly reasonable architectural choice for this requirement:
The application has a constantly updated data structure (I'd not reject calling it schema), so it is important that newer versions of the app must accept old existing data, and preserve obsolete fields that might otherwise be reintroduced in later versions.
Take a look at FossilSCM
It uses a relational database (SQLite), and also uses a flat file document system (called Artefacts).
It has the philosophy that the Artefacts are the store of truth. And indeed what is in each artefact varies considerably both by version, and purpose.
The relational database itself serves two purposes:
A resilient data format for ...