New answers tagged

0

You could either save the requests as they come in (to an event store of some kind), or you could keep multiple versions of the underlying data, such that each change which needs to be tracked creates a new copy of the entity. In choosing between these two, you are making the same tradeoff one always makes: storage cost vs compute cost. Let's say I have ...


0

How does one write & run queries against a database/warehouse if they have no knowledge of how the data is structured? Short answer - you can't. It's like asking how you can do exploratory surgery on someone, but you know nothing about Anatomy. OK, you can do slightly less damage in a database, but the principle is the same. You have to find out ...


2

I'd need to know better the situation in which you had to do that, otherwise my answer may not be what you're looking for. In general you know the structure; either you created the application and thus the tables and obviously then you know their structure, or the person who created it documented to you, or provided a script to create the tables that you ...


5

There's standard system tables that will tell you the names of the custom user-defined tables and their columns and types (metadata packaged as well-known tables & columns).  And this is how some abstract database user interfaces, management tools, and debugging tools can be made to work against any (SQL, user-defined) database that they have never ...


8

A table called CityTranslations could be of help or more generically Translations. Make a FK to the table that has the city column. The table could have a column for the translation value and the language code. Only have records for values that exist so you don't have a column with lots of NULL values. Sounds like you would start with 10 rows and as more ...


1

Taking into account that you're not planning to have a new translation for the next couple of years I prefer the first approach because it eliminates the need for inner join when obtaining English translation. Which in turn would be beneficial for performance. In any case, I suggest you consider if localization could be made somewhere else. I.e. on the ...


1

One drawback of using overly long columns is in run-time memory allocation. SQL Server will use half the declared length when calculating how much memory to request. So for varchar(100) it will be 50 bytes per value. This may not be a problem if the server has sufficient RAM or such queries are infrequent. If, however, it is every row in every table in ...


1

Yes it does smell just a little :) The first improvement you could make is to encapsulate the id comparisons inside the Tag class by providing boolean methods like tag->isDepositPaid() and customer->hasPaidDeposit(). There will be a tiny performance hit - probably impossible to measure as database operations are likely going to be the performance limiting ...


3

Yes, this smells and proves to be very inflexible. If it would just be about important vs. less important tags, I'd just mark those in the tags table with a property is_important. But your code example suggests that there may also some special, application specific behavior behind it. This creates an issue since the application behavior is tightly ...


3

To store hierarchical data in a relational database you have a few choices of techniques. Depending on your use case (read-heavy, write-heavy, implementation complexity, etc.) you may want to evaluate them given your constraints and choose then. Nested sets Example: left right key value 1 6 a 2 5 b 3 4 ...


3

A good rule of thumb is to design the database schema without too much regard to optimizing performance (especially if you're new) and then change it, if necessary, for performance reasons. As Delioth points out, you often won't know the real bottlenecks until it's working. Generally, you are not going to need to change the core table structures to ...


3

If files can be associated either with sessions or subsessions, you should consider using separate association tables for that. Session table Id ... SubSession table Id Session -> references Sessions.Id ... Files table Id FileName ... SessionFiles table Session -> references Session.Id File -> references Files.Id SubSessionFiles table SubSession -> ...


0

Assumed sessions and subsessions have more properties in common than just the property of being a container for files, I would avoid to have two different tables Session and Subsession. Just give Session a nullable foreign key field ParentSessionID. Where this field is NULL, the session is a "normal" one, where it is filled, the session is a subsession. Now ...


0

It's not clear whether you are satisfied with an eventually consistent model, but using commands to create a changelog might be applicable here. Check CQRS for details. This would allow you to store changes (as well as keep an audit log to track changes etc.) in a uniform format (like "update", "entity", "property", "newvalue: 2" type of thing). However the ...


Top 50 recent answers are included