I would suggest the following structure due to my experiences in some other applications.
First of all I would build a language table:
language_id (PK)
iso_country_code
iso_language_code
codepage
translation_id (FK)
The language_id would represent the primary key. Each available language is added to this table.
iso_country_code can contain the ISO country code (GB - Britain, DE - Germany, ...).
iso_language_code can be used to cover different located languages (e.g. en_US, en_GB,...)
codepage is the codepage which will be sent out.
translation_id more on this later on.
The second thing should be a translation table. The translation table should hold back all translations for every translatable term.
translation_id PK
language_id PK (FK -> language)
term
The table consists of an combined primary key over translation_id and language_id which will prevent double insertion.
language_id will refer to the language table.
term itself will just be the translated term (e.g.: Table in german -> Tabelle)
The next thing will happen on each table which holds translatable items. For example a Table which holds your products for example called products.
product_id PK
product_information
product_price
...
translation_id (FK)
Only the translation_id is needed. It will refer to the translation table and retrieve the correct translation. The application can give or user session can be joined into the statement to filter the proper language for the logged in user.
The other positive thing on this solution is that if you have multiple translations in different tables which all means the same but in a different context and all can have the same translation_id, they already can share the same translation_id which will reduce your data weight and will improve the translation.
Hopefully this will give you a good hint and help you to improve your solution.
GUIDs are by definition "Globally Unique IDentifiers". There's a similar but slightly different concept in Java called UUIDs "Universally Unique IDentifiers". The names are interchangeable for all practical use.
GUIDs are central to how Microsoft envisioned database clustering to work, and if you need to incorporate data from sometimes connected sources, they really help prevent data collisions.
Some Pro-GUID Facts:
- GUIDs prevent key collisions
- GUIDs help with merging data between networks, machines, etc.
- SQL Server has support for semi-sequential GUIDS to help minimize index fragmentation (ref, some caveats)
Some Ugliness with GUIDs
- They are big, 16 bytes each
- They are out of order, so you can't sort on ID and hope to get the insertion order like you can on auto-increment ids
- They are more cumbersome to work with, particularly on small data sets (like look up tables)
- The new GUID implementation is more robust on SQL Server than it is in the C# library (you can have sequential GUIDS from SQL Server, in C# it is random)
GUIDs will make your indexes bigger, so the disk space cost of indexing a column will be higher. Random GUIDs will fragment your indexes.
If you know you aren't going to synchronize data from different networks, GUIDs can carry more overhead than they are worth.
If you have a need to ingest data from sometimes connected clients, they can be a lot more robust for preventing key collisions than relying on setting sequence ranges for those clients.
Best Answer
Advantage:
GUID is unique so in the long run if you run into this scenario where you can join using just Guid rather than PK+other field. ex:there sales, order, adjustment, and there is stock, instead of join using PK and transaction type, you can just join using Guid since it 99.9...% guaranteed unique.
By using GUID you can generate GUID from code so it remove a need to do callback to get new PK.
By having auto-increment you can get latest transaction just by ordering auto-increment instead of transaction date(stackoverflow using this method)