32

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 ...


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.


14

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 performance problem is this: there is no "in between" values between two sorted items. item index ----------------- gizmo ...


13

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

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 ...


11

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 ...


10

"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 ...


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

There's at least two other effective means to handle this type of problem. A. Use triggers. Set them to fire on the appropriate operations. B. Expose a service. The client program goes through the service instead of directly hooking into the database. This gives you full control over which tables can be updated. The extra layer gives you the ability to ...


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

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

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

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

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

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

Database triggers will allow you to do this most efficiently. Create a trigger on the insert/delete operations in the relevant table so you capture the event you're talking about. Use the trigger to insert a row into a table just for your application to mark when a record has been changed. Triggers were designed to do this and are very fast, but be careful ...


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

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 ...


4

Basically, the whole thing just doesn't seem like it was 'made for me' and my small micro-blog I think, this is the basic statement here. Although your question is about Do common relational database systems work 'out of the box'?, what you are looking for is more: do I need a full featured DB for microblogging?. And there is a clear answer: No, you don't....


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