چگونگی طراحی جدول برای قاره ها ، کشور ها ، شهرها

[behnam-soft]

سلام خدمت دوستان، می دونم که سوالم خیلی مبتدیانه ست اما من تازه یادگیری اس کیو ال سرور رو شروع کردم، سوالم اینه که چطور می شه در اس کیو ال سرور، ما بیایم یه جدول بذاریم برای قاره ها، یعنی 5 تا قاره رو تو یه جدول داشته باشیم، بعد هر کدوم از این قاره ها یه زیر مجموعه دارن، مثلا قاره آمریکا شامل چندین کشور میشه که اینا هم باز تو یه جدول دیگه هستن، و بعد هر کدوم از این آیتم ها (کشور ها) خودشون شامل چندین شهر هستن.بطوری که اگر کاربر قاره اروپا و کشور مثلا اسپانیا رو انتخاب کرد، تمام شهر هایی که در اسپانیا هست رو بتونه ببینه(که البته این از طریق سی شارپ هست که ما فعلا کاری باهاش نداریم.)
من اومدم جدول هاشو ساختم و خواستم هر کدوم از این قاره ها رو به شهرش مرتبط کنم - هر کدومشون رو به عنوان فیلد کلیدی تعیین کردم- اما ازم قبول نکرد !!!:لبخندساده:
دوستان اگر کسی راهنمایی کنه ممنون می شم!!!:افسرده:

[mahan.2002]

با سلام

شما برای این کار یک جدول قاره در نظر بگیرید و به همین ترتیب جداول تون رو طراحی کنید..

جدول قاره ( کد قاره ، نام قاره )

جدول کشور ( کد کشور ( کلید اصلی ) ، کد قاره ( کلید خارجی ) ، نام کشور)

جدول شهر ( کد شهر ( اصلی )، کد کشور (خارجی) ، نام شهر )

....

به همین ترتیب حتی برای میتونید منطق یا حتی خیابان یا کوچه را هم وارد کنید ولی باید این اطلاعات رو یک به یک در جدول ذخیره کنید...

موفق باشین

[behnam-soft]

ممنون از راهنمایی تون ولی من متاسفانه هنوز خیلی با مفهوم کلید اصلی و کلید خارجی آشنا نشدم !!!:خجالت:
1- یعنی برای هر جدولی که زیر مجموعه یه جدول بالاتر از خودشه، اول باید یه کلید اصلی تعریف کرد و هم یه کلید فرعی که در واقع کلید اصلیه جدول ما قبل خودشه ؟؟؟
2 - اگه ما بخوایم بگیم تمام شهر های کشور اسپانیا رو نشون بده، با استفاده از این کلید ها، به زبون فارسی چطوری میشه توضیحش داد؟؟؟

[mahan.2002]

با سلام دوباره خدمت شما
اگه بخوام ساده توضیح بدم کلید اصلی کلیدی هست که اولا باید واحد باشه یعنی توسط اون بتونیم به اطلاعات مون دسترسی پیدا کنیم
مانند کد دانشجویی توسط اون ما به اطلاعات اون دانشجو دست پیدا میکنیم همون نام نام خانوادگی این چیزا...

خب حالا کلید خارجی چیه ؟ کلید خارجی کلیدی در جدول دیگر کلید اصلی هست و برای پیدا کردن اطلاعاتی جدول دیگر استفاده میشود... مثلا همون دانشجو در یک درسی یا یک ترم ثبت نام کرده
برای اینکه بتونیم ارتباط شون رو مشخص کنیم .. خب باید کد دانشجویی که این فرد، دارد رو ، در جدول دروس باید ثبت کنیم . به این میگن کلید خارجی ... یعنی ما الان کد دانشجویی مون در جدول درس کلید خارجی محسوب میشود. حالا کلید اصلی در جدول درس چی محسوب میشود .؟؟؟؟؟ -----------؟؟؟
...//
......
....
///؟؟؟
حالا باید فکر کنید ؟ خب ما باید برای هر درس یک کلید داشته باشیم که بتوانیم اون درس رو پیدا کنیم پس باید یک کد برای هر درس تعریف کنید.
که این همون کد اصلیه یا کلید اصلی هست
پس یه فیلد بنام کد درس تعریف میکنیم که بعنوان کلید اصلی در "جدول درس" تعیین میکنیم..
البته تعاریف : اکادمی و کتابی هست که میتونید بخونید یا سرچ کنید
ولی من خواستم طوری توضیح بدم که بمفهوم اون اشنا بشن
باز بپرسین بتونم راهنماییتون بکنم..
همه که از اول همه چی بلد نیست .. باید جستجو کرد ، پرسید.. تا بتونه بنتیجه رسید..
االبته هر کسی، ممکنه هر چیزو رو ندونه .. چیزهایی که ما نمیدونم خیلی بیشتر از اونی هستش که با میدونم
پس مهم اینکه تلاش کنیم ..
موفق باشین:لبخندساده:

[behnam-soft]

از توضیحاتت ممنونم، فکر می کنم باید بشینم پایه ای طراحی جدول بخونم تا بتونم کامل با این مفاهیم آشنا بشم. بازم مرسی.

[CodeWizzard]

با سلام

شما برای این کار یک جدول قاره در نظر بگیرید و به همین ترتیب جداول تون رو طراحی کنید..

جدول قاره ( کد قاره ، نام قاره )

جدول کشور ( کد کشور ( کلید اصلی ) ، کد قاره ( کلید خارجی ) ، نام کشور)

جدول شهر ( کد شهر ( اصلی )، کد کشور (خارجی) ، نام شهر )

....

به همین ترتیب حتی برای میتونید منطق یا حتی خیابان یا کوچه را هم وارد کنید ولی باید این اطلاعات رو یک به یک در جدول ذخیره کنید...

موفق باشین

جناب ماهان عزیز، با تشکر از توضیحات عالی شما
سوالی از خدمتتون داشتم اینه که الان با این ساختار، اگه بخوایم بدونیم مثلا فلان شهر در چه قاره ای هست چه دستوری می تونیم استفاده کنیم؟
پیشاپیش متشکر از زحمات دوستان عزیز...

[Reza_Yarahmadi]

شما برای این کار یک جدول قاره در نظر بگیرید و به همین ترتیب جداول تون رو طراحی کنید..

جدول قاره ( کد قاره ، نام قاره )

جدول کشور ( کد کشور ( کلید اصلی ) ، کد قاره ( کلید خارجی ) ، نام کشور)

جدول شهر ( کد شهر ( اصلی )، کد کشور (خارجی) ، نام شهر )

....

به همین ترتیب حتی برای میتونید منطق یا حتی خیابان یا کوچه را هم وارد کنید ولی باید این اطلاعات رو یک به یک در جدول ذخیره کنید...

موفق باشینمشکلاتی که این نوع طراحی داره :
- توسعه نرم افزار بسیار سخت میشه (مثلا در صورت طراحی تا سطح شهر اگر تمایل به اضافه کردن سطح خیابان و کوچه داشته باشید باید جداول جدید ایجاد و SPها و UFهای برنامه رو آپدیت کنید لایه های برنامه هم ...)
- در صورتیکه تعداد سطوح بالا بره ، بدلیل تعداد Join بالا بین جداول Perfomance برنامه پایین میاد.
- اگر بخواید Join بین جداول آدرس نداشته باشید برای ذخیره یک آدرس باید به ازا هر جدول یک فیلد در جدول مورد نظر ایجاد کرد (فرضا برای آدرس هر مشتری باید توی جدول مشتری فیلدهایی برای مشخص کردن قاره ، کشور ، شهر و ... گذاشته بشه)


یک راه حل دیگه میتونه بصورت زیر باشه
جدولی طراحی کنید با فیلدهای زیر
ID , Name, ParentID
فیلد ID از نوع عددی و ترجیحا Identity
فیلد Name از نوع Nvarchar برای ذخیره نام قاره/کشور/شهر/...
فیلد ParentID اشاره به سطح بالاتر از سطح جاری
مثال:
ParentID Name ID
---------------------------
1 آسیا NULL
2 ایران 1
3 ژاپن 1
4 چین 1

5 تهران 2
6 اصفهان 2
7 پکن 4

برای بدست آوردن آدرس کامل با استفاده از CTE میتونید این کار رو انجام بدید (توی سایت جستجو کنید)
تعداد سطوح برنامه تا هر چقدر بخواید میتونه پیش بره
نمایش درختی اطلاعات براحتی امکان پذیره
و...

[veniz2008]

- اگر بخواید Join بین جداول آدرس نداشته باشید برای ذخیره یک آدرس باید به ازا هر جدول یک فیلد در جدول مورد نظر ایجاد کرد (فرضا برای آدرس هر مشتری باید توی جدول مشتری فیلدهایی برای مشخص کردن قاره ، کشور ، شهر و ... گذاشته بشه)

سلام.
دوست عزیز من یکی دو سوال برام پیش اومد.
منظورتون از قرار دادن فیلد در جدول مشتری ذخیره مستقیم متن هاست؟ مثلا به جای اینکه کد قاره ذخیره بشه بیایم و نام قاره (مثلا آسیا یا اروپا) رو ذخیره کنیم؟. ممنون میشم بیشتر توضیح بدید.
سوال دوم : در این مدل طراحی که فرمودید اگر اون فیلدهایی که گفتید داخل جدول اضافه بشن امکان null بودن رو داشته باشن، آیا این تعداد فیلدها که میتونه زیاد هم باشن باعث ایجاد جداول sparce نمیشن؟ و روی performance تاثیر منفی نمیذارن؟

[Reza_Yarahmadi]

منظورتون از قرار دادن فیلد در جدول مشتری ذخیره مستقیم متن هاست؟ مثلا به جای اینکه کد قاره ذخیره بشه بیایم و نام قاره (مثلا آسیا یا اروپا) رو ذخیره کنیم؟. ممنون میشم بیشتر توضیح بدید.
نه، منظورم این بود که کد قاره ذخیره بشه. این حالت فقط برای مواقعی میشه ازش استفاده کرد که نیاز به واکشی اطلاعات جمعی نباشه و همیشه بصورت تک تک رکورد ها واکشی بشه در این حالت به جای Join بین جداول با یه جستجو ساده بین جداول قاره ، کشور و... مقدار آدرس بدست میاد.(این روش درستی برای ذخیره آدرس نیست ولی توی یه برنامه به اصلاح بزرگ به این روش ذخیره کرده بودن!!) در حالت عادی برای بدست آوردن آدرس باید بین همه جداول Join بزنید و Join بین بیش از سه جدول به هیچ عنوان توصیه نمی شه مخصوصا اگه تعداد رکورد های اون هم زیاد باشه.

در این مدل طراحی که فرمودید اگر اون فیلدهایی که گفتید داخل جدول اضافه بشن امکان null بودن رو داشته باشن، آیا این تعداد فیلدها که میتونه زیاد هم باشن باعث ایجاد جداول sparce نمیشن؟ و روی performance تاثیر منفی نمیذارن؟
اول اینکه نوع این فیلد عددی است و 4 بایت فضا میخواد و این مقدار بسیار ناچیزه.
دومین موضوع اینه که فقط قاره ها مقدار ParentID اونها مقدار Null میگیره که تعداد اونها معلومه.
اگر Index گذاری درستی روی فیلدها باشه سرعت واکشی بسیار بالا است.

مهمترین مسئله توی این مدل طراحی امکان اضافه کردن سطوح به هر اندازه است

[محمد سلیم آبادی]

نمیشه بدون دانستان تمام جزئیات بهترین جواب را داد.
باید دید که به چه منظوری قرار هست اسامی قاره ها، کشورها و شهرها و تعلقات آن ها را در پایگاه داده ذخیره کنید.
بطور نمونه آیا قرار هست که برای هر قاره در کنار اسم آن خصیصه های آن را نیز داشته باشید؟ مثل جمعیت، وسعت، زبان، دین... ؟
آیا قرار هست در جداول دیگه مثل جدول دفاتر (office) برای ستون شهر (شهر که دفتر در آن وجود دارد) قید کلید خارجی گذاشته شود تا تنها شهرهای معتبر در آن درج بشن؟
اگر جواب سوالات فوق مثبت باشه دیگه نمیشه این جداول (قاره، کشور و شهر) را با هم ادغام کنید.
قبل از ادامه مطالب فرض میکنیم سه جدول زیر را داریم
قاره ها (کلید اولیه: نام قاره - و سایر ستونها)
کشورها (کلید اولیه: نام کشور – کلید خارجی: نام قاره – و سایر ستون ها)
شهرها (کلید اولیه: ترکیبی از نام شهر و نام کشور یا یک مقدار identity – کلید خارجی: نام کشور – سایر کشورها) نکته: هنوز کلید خارجی به منظور نام قاره در این جدول لحاظ نشده است.
حالا بر میگریدم به پرس و جوهایی که قرار هست روی این جداول صورت بگیرد. خب فرض میکنیم که میخواهید اسامی شهرهای کشور x را اسخراج کنید.
در این حالت کافیه که یک select روی جدول شهرها بزنیم تا نتیجه بدست بیاید. مثل این
Select * from Cities where country = 'X'
نکته ای که باید ذکر بشه این هست که برای جدول کشور ها و قاره ها از کلید طبیعی (نام قاره یا کشور) برای کلید اولیه استفاده شده است. به این علت که اسامی قاره ها و کشورها منحصربفرد است (ممکن نیست که دو قاره با نام یکسان داشته باشیم یا اینکه دو شهر یا چند شهر با نام یکسان در دنیا وجود داشته باشه)
(اگر اسامی شهرها هم در دنیا منحصربفرد باشه میشه برای کلید اولیه چدول شهرها نیز از نام شهر استفاده نمود)
حالا یک پرس و جوی دیگه رو مورد بررسی قرار میدیم. اسامی شهرهایی را انتخاب کنید که در قاره آسیا وجود دارد.
جواب1: باید توسط inner join بین جدول شهرها و کشورها، شهرهایی را انتخاب کنیم که در کشوری وجود دارند که متعلق به قاره آسیا هستند.
به این صورت
Select c.* from cities c inner join country on c.country=country.name
Where country.name = 'Asia'
خب. در query فوق یک inner join مشاهده میشه. حالا چطور میشه از آن بی نیاز شد؟!
دو راه شناخته شده وجود داره یکی ناهنجاری (anomaly) و دیگری ایجاد یک indexed view. در اینجا روش اول رو مورد بررسی قرار میدم.
داده هایی که مورد بررسی هستند عموما به هنگام سازی خاصی ندارند (static هستند). یعنی بعد از اینکه داده ها در جداول مستقر شدند دیگه نام آنها و تعلقاتشان بروز رسانی نمی شوند (اگر شما داده ها را بصورت صحیح وارد کرده باشید)
چطور بشه که یک کشوری مثل ترکیه از قاره آسیا به قاره اروپا تغییر وضعیت بدهد؟!
در نتیجه در این موارد که بروزرسانی خاصی وجود ندارد ما میتوانیم از هنجارشکنی برای افزای کارایی و performance پرس و جوهایمان استفاده کنیم.
بنابراین برای بی نیازی از inner join در پرس و جوی اخیر کافیه یک ستون به جدول شهرها اضافه کنیم و نام قاره ای که آن شهر در آن قرار گرفته است را نیز لحاظ کنیم.
پس پرس و جوی اخیر با لحاظ ناهنجاری به این صورت در میاید.
Select * from cities where Qareh = 'Asia'

[veniz2008]

اول اینکه نوع این فیلد عددی است و 4 بایت فضا میخواد و این مقدار بسیار ناچیزه.
دومین موضوع اینه که فقط قاره ها مقدار ParentID اونها مقدار Null میگیره که تعداد اونها معلومه.
اگر Index گذاری درستی روی فیلدها باشه سرعت واکشی بسیار بالا است.

مهمترین مسئله توی این مدل طراحی امکان اضافه کردن سطوح به هر اندازه است
ممنونم از پاسخگویی شما ولی منظور من از سوال دوم همون پیشنهاد اولتون بود نه پیشنهاد مربوط به سطح بندی.
یعنی با فرض قرار دادن فیلدها در یک جدول (به ازای هر جدول یک فیلد)، اگر اون فیلدها مقدار null بتونن بگیرن و این مقادیر null زیاد باشن.(وارد کردن قاره، کشور ، شهر و ... اختیاری باشه). در اینصورت جدول sparce تولید نمیشه؟ و روی performance تاثیر منفی نمیذاره؟



فرض میکنیم سه جدول زیر را داریم
قاره ها (کلید اولیه: نام قاره - و سایر ستونها)
کشورها (کلید اولیه: نام کشور – کلید خارجی: نام قاره – و سایر ستون ها)
شهرها (کلید اولیه: ترکیبی از نام شهر و نام کشور یا یک مقدار identity – کلید خارجی: نام کشور – سایر کشورها) نکته: هنوز کلید خارجی به منظور نام قاره در این جدول لحاظ نشده است.

آقا سلیم این روشی رو که گفتید که از نام ها استفاده میکنیم در موقع جستجوی رکوردهای زیاد (مثلا لیست تعداد شهرهای یک قاره) باعث کاهش سرعت نمیشه؟(منظورم استفاده از فیلد رشته ای به جای فیلد عددی در شرط کوئری هست).
اینکه گفته میشه تا حد امکان کلیدها رو عددی بگیرید نه رشته ای در چه مواردی صدق میکنه؟
در جدول شهرها که کلید رو ترکیبی (و البته رشته ای) در نظر گرفتید بهتره یا identity باشه بهتره؟ .چون بر روی کلید clustered index تشکیل میشه آیا کلید ترکیبی اونم از نوع رشته ای در تعداد رکوردهای زیاد باتوجه به احتمال زیاد جابه جایی رکوردها مشکل آفرین نمیشه؟ (با اضافه کردن یک شهر جدید، ممکنه 10 ها هزار رکورد جابه جا بشن تا رکورد جدید در جای خودش قرار بگیره).
ممنون میشم اگر روش index view رو هم با ذکر یک مثال توضیح بدید.

[محمد سلیم آبادی]

الزامی نیست حتما کلید اولیه شاخص خوشه ای باشه. کلید اولیه همان کلید طبیعی(خصیصه ای که از موجودیت است) باشه ولی از نوع شاخص غیر خوشه ای منحصربفرد. برای شاخص خوشه ای هم از مقدار getdate میشه استفاده نمود تا مشکل fragmentation بوجود نیاد. به این علت که مقادیر تولید شده توسط تابع getdate همیشه sequential خواهند بود.
وقتی از کلید طبیعی (natural key) استفاده میکنید به عنوان pk جستجو ها ساده تر میشه. یه join حداقل کمتر میشه.
کلید مصنوعی
Select * from cities where countryID = (select countryID from country where countryName=Iran)
کلید طبیعی
Select * from cities where countryName = Iran
زمانی که از کلید مصنوعی به عنوان pk استفاده میشه دلیل بر این نیست که کلیدهای طبیعی به حال خودشون رها بشن. باید حتما برای آنها یک unique constraint لحاظ نمود.
البته توجه داشته باشید که وقتی pk ترکیبی هست باید در جداول دیگه از composite foreign key استفاده کنید.


ساخت indexed view هم کار ساده ای فقط چند نکته هست که باید هنگام ساخت به اونها توجه کنید. یکی آپشن with schemaBinding هست که باید بهش اشاره بشه و دوم اینکه باید نام schema تمام object ها رو هم در query مشخص کنید. و در نهایت اگر میخواهید روی ویو تولید شده شاخص تعریف کنید باید اولین شاخص یک unique clustered index باشه.


فرض کنید میخواهیم به جای هنجارشکنی در مثال قبلی از این روش استفاده کنیم. ابتدا نیاز داریم به یک query تا نام قاره هم برای هر سطر از جدول شهر داشته باشیم (به dbo توجه داشته باشید).



select *, dbo.countries.QarehName from dbo.cities

inner join dbo.countries

on dbo.cities.countryName = dbo.countries.countryName



سپس همین کوئری را بعد از as می نویسید.


create view indexed_view_sample with schemaBinding as

و در پایان یک شاخص منحصربفرد خوشه ای روی ستون نام شهر (که یکتاست) ایجاد میکنید.

create unique clustered index ix on indexed_view_sample (CityName)

توجه داشته باشید. بعد از اینکه شاخص روی این ویو ایجاد شد از این پس دیگه داده ها بصورت فیزیکی روی hard disk ذخیره میشن. و هر گاه داده های جداولی که در ویو نقش دارن تغییر کنن این ویو نیز اتوماتیک باید مقادیرش بروزرسانی بشه.