defining schema name

Hi All,

I am creating student table in SQL Server 2012. I thought to create separate schema for each module. Like Attendance, Assessment, etc. However student table will be used in all of them or most of them. In that case is it a good idea to create separate schema like 'common' or use one of the available schema even though table is used across.

Also i have some confusion on using varchar vs. nvarchar. As of now i do not have any ideas to use any foreign languages. But in future i might have a need to do but may not be in all tables just few tables like getting student names or grade details. in this scenario should i create them with nvarchar or no? For ex for student name i would like to have the name in english and also in foreign language. How to handle this?

Thanks for your time.
JyozealAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
> separate schema
you can do this as you wish, as in the end, all of the tables are accessible (given permissions are granted to the user) from any schema. it's basically nothing else than a naming/grouping, and eventually the possibility to grant permissions to all the objects in a schema  (for example grant select on a schema will allow the user to select data from any tables from that schema, even for tables added after the grant.

varchar vs nvarchar: if your data only stores "national" characters, the character set depends on the db settings. as soon as the data could be international (and names typically are) you should use nvarchar to be able to properly store any names in it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
I thought to create separate schema for each module
Why not create a table named module and create a relationship between Student and Module tables?

As of now i do not have any ideas to use any foreign languages
Are you talking about foreign languages like French, Spanish and German or those ones like Arabic, Chinese and Japanese? If it's the first ones then you can keep the varchar. For the last ones then use the nvarchar data type.
Scott PletcherSenior DBACommented:
Typically you leave all the tables in a single schema unless you have a real need, typically a security requirement, to put them in different schemas.  Using different schemas increases security administration and SQL overhead.

For student name, I'd strongly suggest using nvarchar, just in case you need it later.  You don't want to improperly display a student's name.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JyozealAuthor Commented:
Thanks all for your comments. I decided to go with nvarchar for names.
ScottPletcher, i thought using schema will help in grouping the tables and SPs in logical fashion.
 I do not think i will need it from security perspective.

Vitor Montalvão, i think its second category. So when i need only few fields in second language should I add them as additional fields in table or how to implement it ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
So when i need only few fields in second language should I add them as additional fields in table or how to implement it ?
You mean that you'll have fields for second language? I've thought that you wanted a field that could store characters for any language and that's should be a nvarchar field.
JyozealAuthor Commented:
Thanks for your reply. Assume the name is John. I will have John under firstname. However when i need to send an email i need to use foreign language. So i need to have english and foreign language version too. In this case is it better to use seperate table for these mappings. Like for ex: Teacher provides comments in english. But while sending the same to student's parents(who are non english speaking) i would like to translate teacher comments. So when i save those comments i need to use nvarchar right? So in this case student name and comments from teacher should be available in foreign language.
Vitor MontalvãoMSSQL Senior EngineerCommented:
How you'll do for translating the comments from English to a foreigner language?
Also, usually you don't translate names.
JyozealAuthor Commented:
Vitor Montalvão, i did not still think about it.  translating names in the sense writing the name in foreign language.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should think about that before going to a solution. For example, if you're using a translator webservice or similar component you may not need at all a translated column. Everything you'll need to do is to pass the text to the component to translate it for you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.