Is this what you mean by paginating limiting the result set to only the data you want?
For example: Imagine a table with peoples names and ages. You want a result set with only people younger than 18. Is this the type of thing you want to do?
In SQL , this is done through a where clause.
Select *
from tableofNamesandAges
where age < 18
Sorting is done with an order by clause. Same table, still only want under 18, but you also want them in age order.
Select *
from tableofNamesandAges
where age < 18
order by age
In MS SQL Server a clustered index on a column will cause the data to be stored in the order of the index. Results will return as if ordered by the indexed column, even if no order by statement is used. Only one column per table can have a clustered index. I wouldn’t base my clustered index on how I want things ordered, I would use an order by statement.
It sounds like you are trying to separate result sets by location. Perhaps for a report?
Are the properties files separate files from the database? I would have that data stored in the database.
A simple model for this would have 4 tables:
1.Main data table
2.table for locations
3.table status codes
4.table linking status codes to location, with the translated “display message”. For every status code/location combination, there should be a message. I am assuming that each location has the same status codes, and you just want to display it in the location language.
The main data table would have keys indicating location and status codes. You use the combination of those two values to get the correct message to display. The schema is similar if this is database that supports reporting or an application. IT might be different if the users are distributed geographically.
You mentioned wanting meaningful keys. Performance wise, at least in SQL server, this is not the best approach. SQL server will much more efficiently handle things if the are simple integer values. That is part of why status codes and locations are in their own tables. The text values are stored there, and can be used to display in an application or a report, but SQL code used to get the data should use the key values.
To get the specific data you want for a report or application, construct the appropriate SQL statement.