Tag Info

New answers tagged

1

Approach 1 is the cleanest with no redundancy. However I can understand your worries about scanning through a huge table just to count a post votes. So approach 2 which features some "caching" seams fair. The downside is that you add some redundancy but this is not even the main issue. You also lose consistency. It's possible to have a post with 500 votes ...


0

Actually, keeping current and historical data in the same tables saves a lot more than a few where statements can overcome. Keeping data segmented (by year or otherwise) means you have to examine each query and change the target of the query itself. This usually involves dynamic SQL which is (and should be) always the last solution to consider. I've given ...


0

If it bothers you to include WHERE AcademicYear = '2015/16' very often, you could instead create a view which includes this filter: CREATE VIEW CurrentEnrollment AS SELECT * FROM Enrollment WHERE AcademicYear = '2015/16' My understanding is that MySQL does not support materialized views or filtered indices, so you're not going to get any ...


1

How large do you expect the data to grow? No harm in keeping older data in the same table. You could plan to use partitioning and implement date range partitions (per year). Using WHERE statements is completely natural in SQL so don't consider this as "messy" at all.


1

I am reluctant to post this as an answer, because I don't have a tangible example to post with code, but I pointed Mark to Erland Sommarskog's great article, Giving Permissions through Stored Procedures, which has a section on Signing Procedures with Certificates. Cert signing seems to be the right approach for forcing data access through a procedure ...


1

From my answer on stack overflow: http://stackoverflow.com/a/29772759/2534472 Ok, let's have a look at your 2NF attempt: Student studentID | studentName | courseCode | modCode | resultCode Course courseCode | courseTitle Module modCode | modTitle | credits Let's talk about Student first. Your key cannot be studentID, because ...


0

MongoDB uses flexible schema and you do not have to predeclare your field types in the database. MongoDB (as at 3.0) does not provide any server-side schema validation, so it's perfectly allowable to have a field with different types within the same collection or to have fields vary by document within the same collection. Schema declaration & validation ...


2

As it appears on the manual, use the option --difftype=sql in order to obtain the results as ALTER TABLEs. You have an example on a recent MySQL Performance Blog post: $ mysqldiff --force --difftype=sql \ --server1=root:msandbox@127.0.0.1:21489 \ --server2=root:msandbox@127.0.0.1:21490 \ employees:employees



Top 50 recent answers are included