New answers tagged

5

It somewhat unlikely but possible that someone has built code that looks for this specific error and handles it in some special way that is different from what it would do if there were no results. Maybe they might return an error to the user. Such an approach is bad-practice IMO but I've seen (much) worse. I think that it's a mistake that this syntax ...


2

the database has to go through tens of thousands of comments to find the few ones that match your article No, it doesn't. This concept is called indexes. In basic terms, when you expect to do a search for something within a table, based on a specific column, you define this column as the index. Later, when you ask your database to return all items where ...


0

I have two comments about your design: How do you print a whole price list composed of several products? Do you consider all rows with the same customer and same start and end date as being a single price list? Or you just don't have price lists and instead you have separate prices for different products without bundling them into price lists? You should ...


0

First you need to be clear on what SQL is and is not. SQL is a query language and data manipulation language used for accessing and manipulating data in a relational database. But the schema and data objects (tables, columns, indexes, constraints) in the database are not "in SQL", SQL is just one possible language for querying and manipulating the data. In ...


0

How would you select the most recent record? Like this? select * from tbl where StartDate = (select max(StartDate) from tbl) That's a little cumbersome and seems inefficient though I haven't tested it. Consider having a boolean isMostRecent column. This column is 0 for all records except the most recent record. In this way, here's how you'd select the ...


-1

I ran into a problem wherein serial numbers were being stored as 10-digit decimal numbers in a database, and read into 32-bit integers in Java. This was fine until we hit our first serial number that was larger than 2G, so it could not be represented in Java's 32-bit signed integer. Understanding the DB data types might have prevented this issue.


1

This is an optional 1 to 1 relationship or a 1 to (0,1) relationship. A true identity relationship would be 1 to (1,1). I find this notation useful in understanding the scale of the relationship. A one to many relationship could be 1 to (0,10), 1 to (1,5), 1 to (1, *). The fist digit is always 0 (optional) or 1 (mandatory) while the second specifies an ...


1

The data belongs to the organization, not the application. While right now you're using Laravel exclusively, in the future there might be several applications interacting with the database. I'm currently working on an app that started in FoxPro, migrated to ASP classic, and was partially upgraded to ASP.NET. There are at least three applications interacting ...


3

Foreign keys in your database enables data integrity, as you can't delete a parent row if there is a child row in another table. While you can rely on the framework to handle data for you, the framework will not enable data integrity and you will eventually end up with orphan rows in your database. So, my advice is: design the database properly in order ...


0

You should use joins, and limit the columns you select, AND run multiple selects in the single query : (changed your structure slightly to reduce example code) SELECT t1.* FROM t1 LEFT JOIN t2 on t2.t1_id = t1.id WHERE t1.id > 123 and t1.id < 321; SELECT t2.* FROM t1 INNER JOIN t2 on t2.t1_id = t1.id WHERE t1.id &...


3

Such kind of questions cannot be answered in a sensible manner on an abstract level, without the surrounding context. One has to try this out using the real system: a real database, filled with real data, a real network, a real client machine running a real client application. Then it will be possible to profile both approaches and compare them. And keep in ...


0

I'd leave them in a single table if that's where they are now. Splitting the table might make sense if you have some BLOB fields you want to isolate, but you didn't mention that. I've seen tables like this (with many columns that are mostly NULL) with almost 100 columns. They're ugly to work with, but you should ask yourself how much labor would it ...


1

The point of a database transaction is so you can be sure that several facts within the database are true simultaneously, despite there being other users writing to the same database concurrently. Take the cannonical example of transferring money between bank accounts. The system must ensure that the source account exists, has sufficient funds, the ...


1

Triggers fire synchronously with the transaction which wrote the data initially. That Tx will incur further latency because of the trigger. This may be acceptable to you, or not. If the trigger code fails the original Tx will fail too. Change Data Capture may be a reasonable approach. It can be asynchronous at runtime. The application will be ignorant of ...


1

The cost of ETL should be cheaper because there's some level of batching involved, rather than doing work for every INSERT/UPDATE. But as the time requirement becomes smaller, trigger becomes more suitable answer. On the other hand, if it can be implemented using trigger (e.g. copy to another table), maybe you don't really need to use ETL. You need ETL to ...



Top 50 recent answers are included