database desgin | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

database desgin

I need to know 2 pieces of information about the user : 1- his county(ies) of nationality(ies) and 2 – his country(ies) of Passport(s) thus there are 2 drop down menus that allow the user to select more than one item each. NOTICE: The user may have more than one country of nationality and more than one country of passport and it is not necessairly that he holds the passports of all his countries of nationalities. (I do not know exactly how many values for each he/she may add). Problem: My problem is in the desgin of the db. Now I have 2 options that I can think of it: First Option: Desgin a table in which the values of both: Country(ies) of nationalities and country(ies) of passports are stored. The suggested fields are nationality id and passort number along with the user id. but according to the code (ASP) the following happens, First I insert the values of the country of nationality each value in a row while a zero or null is iserted for the passport field in all of those rows and then comes the turn of inserting the passport numbers while a null or zero value is added for the country of nationality field in all of the rows of the passport countries. Therefore I end up with unused space placed for unused fields. Second Option: I make 2 different tables, one for countries of nationalities with the suggested fields (user id and country of nationality id) and another one for countries of passports with the suggested fields (user id and country of passports id) which result in increasing the number of tables in the db. Now I do not know which solution is better. You may think this is a silly problem but in fact It is a problem to me because I face it almost in all projects I do, in addition it is not usually 2 more tables in the db or some more unused space in the db but it may be as in this project 4 more tables and much more unused space. Can u tell me another better solution? Thank you
Rasha Zaki
Rasha zaki
Web Developer
Cairo, Egypt
For the razon you give, I prefer Second Option.
But, I suggest wait for others opinions. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
True, I would second Luis opinion to use different tables with foreign key relationship. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Another option which is something in between. I am not saying that it is better than your second option. You could use 1 table for both countries of nationalities and country of passport, with [user_id], [type] and [country id]. type would be nationalities or passport. If tomorrow you need to add another type, you would only need to add a type and not create another table. Bambola.
Option 2 is the way to go since it’s the normalized way to do it. Bambolas version can be used as well if you expect new types. This requiers you to plan for this in your code though and then you could as well plan for new tables. It might be quicker with a type column though if you have a lot of these tables but then you create a dependency on an actual data value in the column. This makes it harder to enforce constraints etc. The table will also be bigger than using multiple tables but they on the other hand require more joins. If the data is moderate in all tables and you have good indexes I would go the normalized way all out. /Argyle
Thanks to all of you, I liked very much the discussion that went among you. I liked the solution of Bambola but I know that the second option is the normalized way. Thus I think the best solution in my case is that if I am expecting a big amount of data to be stored then use the normalized way but if it is a limited amount of data then the Bambols version may solve the problem as I do not prefer (phscologically) to have a large number of tables in the db in fact I do not know if my preference is technically right or not? Thanks again Rasha zaki
Web Developer
Cairo, Egypt
Why don’t you like many tables? <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> The number of tables shouldn’t be a big concern when designing a database. I would recommend to always go the normalized way to begin with and apply proper indexing. If you actually run into performance issues after that (or you spot some obvious problems during design) then I would look into other options like denormalizing data or changing to fewer tables. But there are a lot of problems with that as well, especially with the extra code needed to maintain data integrity when doing inserts, updates and deletes since you might have redundat data or constraints that are dependant on actual data in the columns (which can’t be enforced by SQL Server in an easy way). Also having all data in the same table might be a problem if you have a lot of simultanious users that require locks on the data. Then you might need to horizontally partition the table which presents other types of problems and extra management and planning.<br /><br />So always start the normalized way and check for potential problems there. Just switching over to fewer tables or denormalized data might get rid of one problem but present others that are not obvious to begin with.<br /><br />/Argyle
Sorry to be late on this discussion, but from what you have said at the start there seems to be another option…? The user may have more than one country of nationality and more than one country of passport and it is not necessairly that he holds the passports of all his countries of nationalities. Assuming that a user must have a country of nationality in order to possibly hold a passport for it (i.e. the rule above is not communative) Then you could have one table with a bit flag to say if the user holds a passport for this country… i.e. user_id int not null
country_id int not null
has_passport bit not null Cheers
Twan b.t.w. I think that Bamobola’s answer is normalised… She has just worked on the assumption that the two relationships are subtypes of a user-country relationship. This is perfectly valid for 3NF, which is what I assume people in this discussion mean by normalisation?

Yes the solution with one table is in 3NF if one assumes subtypes. I was talking in general about denormalizing or changing to fewer tables in another way (say through subtypes) are not always problem free. Good suggestion with the third alternative as well. /Argyle
I appreciate all the efforts in this disscussion, the third option really solves the problem in a pretty way (I hope you have came earlier as I have areadly used the second option but I will re-desgin it again). As for the talking about denormalization or changing to fewer tables, I really needed it.(Thank to you)
Many Thanks to all of you Rasha zaki
Web Developer
Cairo, Egypt
]]>