Hot answers tagged application-design
7
You should use a lookup table
Not all clients will use the ENUM in the application
You will get a reporting or MIS or Excel app connecting at some point
How can do "NOT EXIST" otherwise?
You will be asked this
You won't know about client enum changes
Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause
...
6
Sounds like we hold similar views on trigger usage but I'm very interested to hear the opposite opinions from other DBAs. Personally, I view them as a last resort and have only implemented triggers where there is no other option. The exception being INSTEAD OF triggers against views.
Triggers are often the only option when you're enhancing a 3rd party ...
5
The design choices you describe are not directly related to normalization.
I agree there should be a lookup table.
I think an OrderStatusID value would increase redundancy. The status (text) value presumably already satisfies many of the qualities of a good key: unique, stable, narrow, familiar to users, etc. Referential integrity can be applied to VARCHAR ...
5
Have you investigated the MERGE command in SQL 2008? Here is a basic example:
merge YourBigTable ybt
using (select distinct (RecordID) from YourOtherTable) yot
on yot.Recordid = YBT.RecordID
when NOT matched by target
then insert (RecordID)
values (yot.DeviceID) ;
This is basically an "UPSERT" command. Update if it exists, insert ...
4
Overall, triggers have a silent and hidden impact on your data modification performance.
I have seen environments where cascading errors were caused by updating other tables from within a trigger and the updated tables had triggers.
As far as auditting is concerned, a trigger may be used, but if you have the Enterprise edition of SQL Server 2008, or ...
4
100k records in a single non-partitioned table is a relatively trivial number. Nothing to worry about assuming you follow normalization guidelines and index appropriately.
Take a look at some example schemas to avoid schoolboy errors. Study sample databases, like the Microsoft examples for SQL Server. When you've created your schema, post a new question ...
4
I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration.
I am assuming that:
you will have separate application for each industry,
they have not very much in common (they really cover different business aspects)
I think that one of acceptable solutions in ...
3
It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column.
Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are ...
2
In Oracle triggers should be avoided in most situations because they divide a portion of the logic into a separate code area that is harder to debug and only runs as side effect, not directly.
Legitimate exceptions include instead of triggers, integrating with third party code, and setting complex default values. The latter may be the biggest argument ...
2
Everything I've ever learned says "never say never". There will always be cases where you have to go against any established convention and there's just no way around it.
Often times it seems that these situations are caused by older, existing systems; third parties; and architecture already in place. While better solutions may be clear and obvious, the ...
2
I have just the situation described above by Mark
Triggers are often the only option when you're enhancing a 3rd party solution and the extension points are not exposed by the systems API.
My example: a c# application written by another developer using LLBL.gen as the ORM. Any change to the data structure that you want the C# code to use implies the ...
2
Update is not as fast. The trick is to achieve a fast insert is to disable the indexes while data is being inserted.
Consider using this:
-- disable indexes
ALTER INDEX [index_name] ON dbo.import_table DISABLE
-- ... disable more indexes
-- don't use delete if you don't care about minimal logging. truncate is faster
TRUNCATE TABLE dbo.import_table
-- ...
2
First off, taking a step back, do you really need to solve the inventory management problem on the front end? Since you're selling large volumes of a relatively small set of products, it should be relatively easy to manage your inventory so that you are never out of stock or, if you are, it doesn't prevent you from fulfilling orders. There is a great deal ...
2
For this scenario, the database server should not be publicly exposed, for obvious security reasons.
You should expose a public interface (API) of your application, probably by using something like web services. The mobile application instances then only communicate directly with the API.
This separation (decoupling) allows for all sorts of good things to ...
1
Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one
Something along the lines:
update real_table
set ...
where exists (select 1 from staging_table where ...)
insert into ...
select
from stage_table
left join real_table
where real_table.some_col is NULL
delete real_table
...
1
Your sum idea works to a point, and then you will end up with a slow transaction whenever you add or remove items from stock due to scalability issues.
The way I did it for a similar problem, was to maintain a running total table, which would work in your case.
I had an audit table as well for accounting purposes, which had original values in case the ...
1
The first this I would try is to add
where doc.datum>=@od_datuma
and doc.datum <=@do_datuma
because the having clause. Maybe it will give a hint to the query planner.
Then the big question is: why the outer join with the subquery?
Does the " where .. stari_uazi.ART_ID is null " mean that you want to exclude all the rows returning from the ...
1
There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier. This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy. ...
1
The quick and dirty argument that should come up against such policy is the implementation of business logic.
Mundane auditing of data traffic is only one good use of triggers.
Maintenance of those triggers for business logic makes the use of triggers take on a life of its own, as if it is part of the software lifecycle. For some databases like Oracle, ...
Only top voted, non community-wiki answers of a minimum length are eligible
