38

First, don't try to do anything clever with decimal numbers, because they'll spite you. REAL and DOUBLE PRECISION are inexact and may not properly represent what you put into them. NUMERIC is exact, but the right sequence of moves will run you out of precision and your implementation will break badly. Limiting moves to single ups and downs makes the whole ...


33

Same answer from here https://stackoverflow.com/a/49956113/10608 Solution: make index a string (because strings, in essence, have infinite "arbitrary precision"). Or if you use an int, increment index by 100 instead of 1. The problem (performance/complexity) being solved by this solution: there is no "in between" values between two sorted items. item ...


31

If you want to keep the data, then it's not obsolete. Just leave it where it is. It's fine if some class mapped to a table doesn't map every column.


16

"but it seems like that would be quite inefficient" Did you measure that? Or is that just a guess? Don't make such assumptions without any proof. "20 to 50 items per list" Honestly, that is not "a whole lot of items", to me that sounds just very few. I suggest you stick to the "position column" approach (if that's the most simple implementation for ...


14

I have seen people using a self-reference to refer to the previous (or next) value, but again, it seems like you would have to update a whole lot of other items in the list. Why? Say you take a linked-list table approach with columns (listID, itemID, nextItemID). Inserting a new item into a list costs one insert, and one modified row. Repositioning an ...


12

text, varchar and char are all used for different reasons. There are of course implementation differences (how much size they occupy .. etc), but also there are usage and intent considerations. What type you use also tells you something about the kind of data that will be stored in it (or we'd all use text for everything). If something has a fixed length, we ...


12

It depends on what kind of application system you are building: if you are creating an application-centric system which contains just one main application, with a dedicated database specifically for this application, and ideally one team responsible for evolving application and database side-by-side, you can keep all validation logic and also audit logic ...


10

They do work out of the box, for the most part, but you do have to know how to use them. They don't come pre-populated with your application's data model, because they don't know what that is, so they're a blank slate, in that respect. SQLite will support small to medium websites with nominal concurrency and relatively low traffic, which describes probably ...


9

You can use triggers. CREATE TRIGGER notifyMe ON table1 AFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB AutoMailer', @recipients = 'user@example.com', @body = 'The DB has changed', @subject = 'DB Change'; GO


8

Using a column per question makes it difficult to add more questions later. Instead, model the relation between users and questions in your table. I.e. the questions are encoded explicitly as data, not implicitly in the structure of your table. The table would have user_id and question_id columns, likely both with foreign key constraints to another table. ...


8

OK so your situation is that you want the old rows to have property C but the new ones not. This is equivalent to having a class inheritance relationship class All { string A; string B; } class Old : All { string C; } class New : All { string D; } which you would represent on the database with three tables with 1 to 1 relations table ...


7

It's a terrible idea to be honest and there are a handful of immediate issues with that I can think of off the top of my head:- modifying the table structure becomes very difficult because of the number of tables you need to modify. you have to grant rights the app to change the table structure which is opposite to the principle of 'least privilege'. you're ...


6

This is really a question of scale, and use case.. How many items do you expect in a list? If millions, i think gong the decimal route is the obvious one. If 6 then integers renumbering is the obvious choice. s Also the questions is how the lists or rearranged. If you are using a up and down arrows (moving up or down one slot at a time), the i would use ...


6

Short answer: Apache Mesos doesn't provide distributed FS. So, apps have to work with local FS on slaves or you may run any distributed FS alongside Mesos. Mesos is typically deployed together with HDFS, and most of the frameworks that run on top of Mesos can work with HDFS (Hadoop, Spark, Storm, etc.) And in case your app doesn't support any distributed ...


5

OK I face this tricky problem recently, and all the answers in this Q&A post gave many inspiration. The way I see it, each solution has its pros and cons. If the position field has to be sequential without gaps, then you will basically need to re-order the entire list. This is an O(N) operation. The advantage is that the client side would not need any ...


5

A few points from our experience both in terms of maintainability and security. A lot of things like code formatting are things that can be done any number of ways as long as you are consistent and you use white space appropriately. As for what is unsafe from a security perspective the only thing that comes to mind is combining SECURITY DEFINER, EXECUTE, ...


5

Databases are intensely concerned with performance--speed and minimizing storage. In most other parts of the computer world, you are not going to be bothered about how many characters are in your character string; it could be one, it could be the entire contents of an encyclopedia; it's all just a string. In fact, a lot of languages don't even bother you ...


5

It's the classic "link vs copy". I reccomend making a duplicate copy. Although "duplicate" may not be the correct term since you are sharing a snap shot of time. It's "unique" with regard to that context. For example if you have a history table you want to store copies of the data, not foriegn keys. With a history you are not interested in the latest ...


5

It depends - mainly on the life cycle of your database(s) and the usage scenario. Creating the DB structures automatically makes sense if the database is used exclusively (or at least primarily) by your application you expect to have not just one database, but many different db instances of this structure in different places the database is used as some ...


5

Don't do this. Plain and simple, that's a terrible data tier pattern. It is bad for the following reasons: Doesn't scale well at all (rows should be created with new data, not objects) Data access patterns will suffer (it will be a pain, best case scenario, to construct your data queries. Worst case scenario you will cause extremely poor performance and ...


5

This is essentially what an Object/Relational Mapping Framework (such as Hibernate) can do for you. It provides a layer on top of the database that abstracts away the details of querying the database by providing a database-independent layer and query language. Hibernate provides (among other ways of querying the database) HQL for this: from Cat as cat left ...


5

The simplest storage would be: StoreID integer (foreign key) DayOfTheWeek integer (range 0-6) OpenTime time CloseTime time ValidFrom date (starting date when this record is in use) ValidThru date (ending date when this record is in use, NULL for no ending date) All of the above fields should be required (NOT NULL). ValidThru ...


5

You should normalise your database schema. This means that instead of just having a User table, you would also have a Questions table containing information about each question, then you would also have an Answers link table where each row represents a particular user answering a particular question.


4

I want to cover both some pro- and con- issues with stored procs. We use them extensively with LedgerSMB, and our rule is, with a few very specific extension, "if it's a query, make it a stored proc." Our reason for doing this was to facilitate cross-language query reuse. There isn't a better way to do this honestly. In the end the question is always on ...


4

Considering all the above cases, I would like to add one more. The choice of SP may be depends on people choice as well. I personally feel frustrated when people puts very complex logic in SP and I believe such SP is very complex to maintain and debug. Even many cases the developer himself faces problem when debugging in code behind (say language part) is ...


4

It depends somewhat on how you're using the database. If you're using an ORM, you might not notice any issues at all. I switched an application to using Postgresql (for deployment to Heroku), but only after discovering situations where the SQL created by Rails worked fine on SQLite, but not on Postgresql. Invariably, the issues were caused when joins were ...


4

Use a floating point number for the position column. You can then reorder the list changing only the position column in the "moved" row. Basically if your user wants to position "red" after "blue" but before "yellow" Then you just need to calculate red.position = ((yellow.position - blue.position) / 2) + blue.position After a few million re-positions ...


4

It seems like your "storage system" whatever that is has a very simple abstraction interface. It essentially boils down to "here's an id, gimme data". So you can easily define this interface and build your entire app on top of it. Behind the scenes you can continue using PostgreSQL like you do today. And if you want to experiment with flat file storage, it ...


Only top voted, non community-wiki answers of a minimum length are eligible