Table relationships and field names

Jun 22, 2008 at 6:43 PM

Peeps -

So far, I'm only having one problem with my generated code -

I'll use the most ubiquitous table example: Addresses, Address Types, Regions and Countries.  For semantic reasons (I've been reading Joe Celko for too long :) )  I've been leaning toward calling, for instance, the foreign key field between the Address table and the AddressType table 'AddressType' instead of 'AddressTypeID' in both tables.  It allows me to hold the textual description (rather than an integer pointer) right in the address table field  and end up with far fewer joins in subsequent queries.  That , and using "id" in any name denotes a primary key to me, not a pointer to a lookup value. But's that's just the Celko disease. 

I know that having the table name and field name the same is a problem (see AddressTypes table). Should I use the extended properties you've laid out to straighten this out? Will it take car of the other issues I am having, such as 'ID' being appended to 'CountryCode' in various places in the generated.Address class, for instance.

I really don't want to try and alter the templates just yet, changing the database would be preferred.  I'm not that much of a DB purist.

Here's the basics of my problem, if you could point me toward your best practice that would be great.

Crab

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------------------------------------------------------------------------
--Address Table

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Addresses]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Addresses](
    [Address_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Addresses_Address_id]  DEFAULT (newid()),
    [AddressType] [nvarchar](20) NULL,
    [line_1] [varchar](80) NULL,
    [line_2] [varchar](80) NULL,
    [line_3] [varchar](80) NULL,
    [city] [varchar](50) NULL,
    [postalcode] [char](20) NULL,
    [RegionCode] [nvarchar](50) NULL,
    [CountryCode] [nchar](3) NOT NULL,
    [Notes] [varchar](255) NULL,
 CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED
(
    [Address_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
------------------------------------------------------------------------------------------------------------------
--Regions Table

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Regions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Regions](
    [RegionName] [nvarchar](50) NOT NULL,
    [RegionCode] [nvarchar](50) NOT NULL,
    [CountryCode] [nchar](3) NOT NULL,
 CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED
(
    [RegionCode] ASC,
    [CountryCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

------------------------------------------------------------------------------------------------------------------
--AddressTypes Table


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ref_AddressTypes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ref_AddressTypes](
    [AddressType] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_AddressTypes] PRIMARY KEY CLUSTERED
(
    [AddressType] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

------------------------------------------------------------------------------------------------------------------
--Country Table

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Country](
    [CountryCode] [nchar](3) NOT NULL,
    [CountryName] [nvarchar](100) NULL,
    [DisplayFlag] [bit] NOT NULL,
    [ISOCOde] [char](2) NULL,
    [SortOrder] [int] NULL,
    [CultureName] [nvarchar](10) NULL,
    [LCID] [int] NULL,
    [PhoneInputMask] [nvarchar](50) NULL,
    [AddressLayout_id] [int] NULL,
    [UseLocality] [bit] NOT NULL,
    [CurrencyCode] [nvarchar](3) NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
    [CountryCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
------------------------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Addresses_AddressTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[Addresses]'))
ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT [FK_Addresses_AddressTypes] FOREIGN KEY([AddressType])
REFERENCES [dbo].[ref_AddressTypes] ([AddressType])
GO
ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_AddressTypes]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Addresses_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[Addresses]'))
ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT [FK_Addresses_Country] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Country] ([CountryCode])
GO
ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_Country]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Addresses_Regions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Addresses]'))
ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT [FK_Addresses_Regions] FOREIGN KEY([RegionCode], [CountryCode])
REFERENCES [dbo].[Regions] ([RegionCode], [CountryCode])
GO
ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_Regions]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Regions_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[Regions]'))
ALTER TABLE [dbo].[Regions]  WITH CHECK ADD  CONSTRAINT [FK_Regions_Country] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Country] ([CountryCode])
GO
ALTER TABLE [dbo].[Regions] CHECK CONSTRAINT [FK_Regions_Country]
------------------------------------------------------------------------------------------------------------------
Coordinator
Jun 26, 2008 at 8:31 PM

You will run into a problem here because the use the the FK field name in the entity class, and then also create another property with "Id" stripped that will be of the type of the primary table. Hence, 2 properties having the same name in your case. I, frankly, would keep Id on the end of it. You could use the DB extended properties to force the name of the entity-typed property, but still, it would have to be a different name.

In my experiences, my lookup tables still have automatically incremented int's, and I might have another column called Name that is a textual representation of the item. Then I would get the type via address.AddressType.Name.

Jun 27, 2008 at 4:16 AM


markjjordan wrote:

You will run into a problem here because the use the the FK field name in the entity class, and then also create another property with "Id" stripped that will be of the type of the primary table. Hence, 2 properties having the same name in your case. I, frankly, would keep Id on the end of it. You could use the DB extended properties to force the name of the entity-typed property, but still, it would have to be a different name.

In my experiences, my lookup tables still have automatically incremented int's, and I might have another column called Name that is a textual representation of the item. Then I would get the type via address.AddressType.Name.



Great, thanks.  I'll give it a go.