Need help to create table on given Data model diagram

Question

Hi All,

I never created table on the basis of Data model diagram .
I have to create the 3 table on the basis of given Data model diagram.
there are 3 tables
1.md_geographyleveltype
2.md_geographylevel
3.md_geographylevelxref

I have tried to create 2 table but unable to create 3rd table.
CREATE TABLE [dbo].[md_geographylevel](
[type_key] [int] NOT NULL,
[geog_key] [int] NOT NULL,
[Type_description] [nvarchar](50) NULL,
[Store_flag] [nvarchar](10) NULL,
[Type_short_desciption] [nvarchar](50) NULL,
[Type_long_desciption] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
[type_key] ASC,
[geog_key] ASC
)


CREATE TABLE [dbo].[md_geographyleveltype](
[type_key] [int] NOT NULL,
[Type_description] [nvarchar](50) NULL,
[Store_flag] [nvarchar](10) NULL,
[Type_short_desciption] [nvarchar](50) NULL,
[Type_long_desciption] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
[type_key] ASC
)

Kindly review the above table script as per data model diagram
I am getting difficulties to create table script for table md_geographylevelxref
help me to create the table create script for md_geographylevelxref

Regards,

Vipin Jha

---

Thankx & regards, Vipin jha MCP

Answer 1

Are we supposed to read your mind and simply "know" what "difficulties" you encountered and what script you used to create this third table?

And no - you are already on the wrong path.  A table does not have all true "data" columns as nullable. In addition, you have duplicated information by adding columns to geographylevel that are found in the related table geographyleveltype.  That is something that is not dictated by the diagram you posted - so why did you do this? I can't say that I understand why geographylevel includes type_key in its primary key - and I suspect that may be incorrect but no one outside of your organization can truly evaluate the correctness of your model.

Lastly, "store_flag" is defined as nvarchar(10).  Why?  A name like "flag" generally implies that this is a simple switch and has a very small number of meaningful values.  So why did you choose a variable length string as the datatype?

Answer 2

Sorry my crystal ball is broken and trying to read your mind is too much like hard work; can you put up the script you are running to create the third table and the error message?

---

Please click "Mark As Answer" if my post helped. Tony C.