Is there any reason this isn’t a good idea? Is there anything I should be aware of? Yes. Integer IDs are very bad if you need to uniquely identify the same data outside of a single database. For example, if you have to copy the same data into another database system with potentially pre-existing data … Read more
In general, the more mission critical the transactional app and the more sophisticated the reporting requirements, the more splitting makes sense. When transaction performance is critical. When it’s hard to get a maintenance window on the transactional app. If reporting needs to correlate results not only from this app, but from other application silos. If … Read more
Is this the expected way to handle it? No. It is a wrong approach. You should eliminate duplication of data for several reasons. It will cost storage space. As the size of database increases, this will began to become worse. It will make the system complicated. If you are updating a single field, You may … Read more
The Answer The oft repeated answer, which tends to always be the case more often than not is, “it depends.” More specifically, it depends on what your data is and how it will be used. tl;dr Explanation The short tl;dr answer for my specific case (and to cover all future bases) is choice #2 which … Read more
Note This subject is being discussed, and this thread is being referenced in other threads, therefore I have given it a reasonable treatment, please bear with me. My intention is to provide understanding, so that you can make informed decisions, rather than simplistic ones based merely on labels. If you find it intense, read it … Read more
You need to set the “default value” for the date field to getdate(). Any records inserted into the table will automatically have the insertion date as their value for this field. The location of the “default value” property is dependent on the version of SQL Server Express you are running, but it should be visible … Read more
Survey Database Design Last Update: 5/3/2015 Diagram and SQL files now available at https://github.com/durrantm/survey If you use this (top) answer or any element, please add feedback on improvements !!! This is a real classic, done by thousands. They always seems ‘fairly simple’ to start with but to be good it’s actually pretty complex. To do … Read more
Think carefully about the requirements for revisions. Once your code-base has pervasive history tracking built into the operational system it will get very complex. Insurance underwriting systems are particularly bad for this, with schemas often running in excess of 1000 tables. Queries also tend to be quite complex and this can lead to performance issues. … Read more
A data file that can hold data for one or more InnoDB tables and associated indexes. There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are, a. System tablespace b. File per tablespace c. General tablespace System tablespace contains, InnoDB data dictionary. DoubleWrite Buffer. Change buffer Undo … Read more
Definitely store them in the main table with a column to indicate whether the data is approved or not. When the change is approved, no copying is required. The extra work to filter the unapproved data is the sort of thing databases are supposed to do, when you think about it. If you index the … Read more