Class Design

I'm working on a web app that keeps track of some user/event information and in the course of roughing out classes I find that I am duplicating alot of the same fields. For example, states and countries. My states and countries are made up of the following fields: ID, Name, Shortname. So what makes more sense?

public class Person
{
	private int id;
	private string firstname;
	private string middlename;
	private string lastname;
	private string city;
	private int stateID;
	private string state;
	private string stateShortname;
	private int countryID;
	private string country;
	private string countryShortname;


	public int ID
	{
		get {};
		set {};
	}


	public string Firstname
	{
		get {};
		set {};
	}


	public string Middlename
	{
		get {};
		set {};
	}


	public string Lastname
	{
		get {};
		set {};
	}


	public string City
	{
		get {};
		set {};
	}


	public int StateID
	{
		get {};
		set {};
	}


	public string State
	{
		get {};
		set {};
	}


	public string StateShortname
	{
		get {};
		set {};
	}


	public int CountryID
	{
		get {};
		set {};
	}


	public string Country
	{
		get {};
		set {};
	}


	public string CountryShortname
	{
		get {};
		set {};
	}
}

OR should I try to do something more object oriented like this:

public class Person
{
	private int id;
	private Name name;
	private string city;
	private State state;
	private Country country;
	
	public int ID
	{
		get {};
		set {};
	}

	public Name Name
	{
		get {};
		set {};
	}
	
	public string City
	{
		get {};
		set {};
	}

	public State State
	{
		get {};
		set {};
	}

	public Country Country
	{
		get {};
		set {};
	}
}

public class Name
{
	private string first;
	private string middle;
	private string last;

	public string First
	{
		get {};
		set {};
	}

	public string Middle
	{
		get {};
		set {};
	}

	public string Last
	{
		get {};
		set {};
	}	
}

public class State
{
	private int id;
	private string name;
	private string shortname;

	public int ID
	{
		get {};
		set {};
	}

	public string Name
	{
		get {};
		set {};
	}

	public string Shortname
	{
		get {};
		set {};
	}
}

public class Country
{
	private int id;
	private string name;
	private string shortname;

	public int ID
	{
		get {};
		set {};
	}

	public string Name
	{
		get {};
		set {};
	}

	public string Shortname
	{
		get {};
		set {};
	}
}

I guess I am just looking for suggestions and/or a reality check on this. I feel like I might be over-engineering this, but it does make the code read much better using the intellisense.

Re: Class Design

Hi TonyC, 1. I do not know if there is an equivalent relationship between Datbase design and Class design. However database designwise, I see 3 tables - see point 2 - Classwise, I see we have a 'Composite' pattern for your classes. I see 3 classes - see point 3 - 2. Database design

--Person table
id (personid)
firstname
middle name
lastname
stateid   (Referential Integrity with State table)




--State table
stateid
statename
stateshortname
countryid (Referential Integrity with Country table)


--Country table
countryid
country
countryShortname

3. Class design

--Person class
id (personid)   	int
firstname		string
middle name	string
lastname		string
State 		object of State class


--State class
stateid		int
statename		string
stateshortname	string
Country 		object of Country class

--Country class
countryid		int
country		string
countryShortname	string

Re: Class Design

Hey Phuoc, What you have is very close to what I have currently in the database. One slight difference though. If someone is from a country that does not have state/province, the Person.State field becomes optional. So would you still organize it the same way or do something like this:

--Person class
id (personid)    int
firstname string
middle name string
lastname string
State  object of State class
Country object of Country class


--State class
stateid int
statename string
stateshortname string
Country  object of Country class


--Country class
countryid int
country string
countryShortname string

Re: Class Design

Hi TonyC, 1. ::One slight difference though What you have is very close to what I have currently in the database. One slight difference though. If someone is from a country that does not have state/province, -Yes. This is business requirement for some foreign countries that I did not see from your requirement. No problem. That's was the reason I did not include Country object into the Person class. So the Person table --Person table id (personid) firstname middle name lastname stateid (Referential Integrity with State table) countryid (Referential Integrity with Country table) -Then "how do you enforce the referential integrity for optional state?" this is the key point here because state can have NULL value or a value matching the state table. How do you enforce RI for NULL value unless you have one null value in your state table? which implies there must be one NULL value in the Country table by the reason of RI between Country and State tables. -In the case for a person with a state and country, then that person will have 2 values for those 2 columns and the state table will also have a country value as well. 2. This point 2 depends on the point 1's outcome. However, I've a question: :: --Person class id (personid) int firstname string middle name string lastname string State object of State class Country object of Country class The fact we have Country object would handle point 1 (business rule). But then the programming implication is we have to check if state object is not optional (not null/nothing) then we have to ignore country object else ignore state and use country object. Is this what you have in mind? 3. We'll have a comprehensive look again based upon your answer for point 1 and 2.

Re: Class Design

TonyC, Additional note for previous point 1 of previous post: -If State can have a NULL value to enforce R.I for optional state, then you must be careful during the Insert into the Person Table which implies your front-end validation must be strict and well tested because people can enter a NULL for state by mistake then NULL can be accepted since state table has a NULL. Your code would not be able to know whether the customer is having optional state or not.

Re: Class Design

Just to kind of clean the code up a bit so we can clearly see what we are discussing (I realize our naming conventions are alittle different but bear with me).

--- Person Table
ID int (PK)
Firstname varchar(50)
Middlename varchar(50)
Lastname varchar(50)
State_ID int (FK for State table)
Country_ID int (FK for Country table)


--- State Table
ID int (PK)
Name varchar(50)
Shortname varchar(5)
Country_ID int (FK for Country table)


--- Country Table
ID int (PK)
Name varchar(50)
Shortname varchar(5)


--- Person Class
ID int
Firstname string
Middlename string
Lastname string
State object of State class
Country object of Country class

1. Currently, I just do left joins on the Person and State tables. If there is no match for the state, the sproc returns null values for state information. There is another left join for the country that works the same way. I tinkered with putting a null placeholder in the State and Country tables, but I didn't like the way that worked. Maybe I'm wrong here. 2. I'm struggling with that part too. The trick to that would probably have to be in the properties of the Person class. On a set for the State, you would copy the state in to the Person.State and copy the Person.State.Country to the Person.Country field. Or you could set the Person.State and skip a set for the Person.Country. On the get for Person.Country, you could first check if Person.State is null. If it is, then return the country field, otherwise return Person.State.Country. Does that help/make sense?

Re: Class Design

Tony, ::Currently, I just do left joins on the Person and State tables. If there is no match for the state, the sproc returns null values for state information 1 quick question before I proceed further: Right now, is your database already set up and used in production OR is it in design mode as we discussed now?

Re: Class Design

It's not in production. This is just my little pet project that is more/less in design mode.

Re: Class Design

Tony, 1. Before we consider my question #2 and your #2 (which are about Class design). Let's have a common ground first. -My question # 1 is concerned about designing first which focused on R.I for the optional state requirement. See Posted: 08-05-2003 11:22 AM, Posted: 08-05-2003 11:45 AM -Your # 1 (Posted: 08-05-2003 12:00 PM) is about data access (SELECT) which is OK but that would not be possible at this stage since my question # 1 has not been fully answered yet. Please read that question again and let me know if we're on the same level. In addition to my question#1 on the previous 2 mentioned posts: How can you insert a row into a Person table for a optional state given there is a R.I between Person table and State table. E.g: -INSERT INTO PERSON VALUES (2, 'TONY', 'C', 'TONYLASTNAME', 'what is the value for optional state that would match the value in State table by R.I' ,1) 3. If possible, set up a sample of data/values for rows for the 3 tables that you see from your point of view.

Re: Class Design

Phuoc #1: The answer is null. If there is no state selected, I just insert a NULL. There is no row in the State or Country table that represents a blank. Should there be? Does that answer your question?

INSERT INTO Person (Firstname, Middlename, Lastname, State_ID, Country_ID) VALUES ('Gerry', NULL, 'MacNamara', NULL, 3)

Phuoc #3:

Person Table
ID      Firstname       Middlename      Lastname        State_ID        Country_ID
------- --------------- --------------- --------------- --------------- ----------
1       Tony                      C               1               1
2       Konstantine               Pavlov                    2
3       Gerry                     MacNamara                 3

State Table
ID      Name    Shortname       Country_ID
------- ------- --------------- ----------
1       Texas   TX              1
2       Utah    UT              1
3       Alaska  AK              1

Country Table
ID      Name            Shortname
------- --------------- ---------
1       United States   USA
2       Russia          RUS
3       Ireland         IRE

Re: Class Design

Tony, 1. ::Phuoc#1,3 I got what I 'suspected' from your answer. It looked like you did not focus on the R.I (referential integrity) concept. 2. Technically, your INSERT statement 'work' because you *did not* create a referential integrity between a Person table and State table for this column : State_ID int (FK for State table) -- from Person Table. In your INSERT or in the Person table, you have NULL for State_ID column but where is the NULL value for the ID column of State Table. 4. If you create a R.I for State_ID int (FK for State table) -- from Person Table. then your INSERT won't work since there is no NULL value for ID in State Table. Btw, what is your database? 5. Example of R.I : FOREIGN KEY constraints identify the relationships between tables. SQLSERVER2000 syntax. CREATE TABLE order_part (order_nmbr int, part_nmbr int FOREIGN KEY REFERENCES part_sample(part_nmbr) ON DELETE NO ACTION, qty_ordered int) GO 6. So we have to solve this R.I first. Read again carefully and slowly: -My question # 1 is concerned about designing first which focused on R.I for the optional state requirement. See Posted: 08-05-2003 11:22 AM, Posted: 08-05-2003 11:45 AM

Re: Class Design

Hey Phuoc, I'm not sure what you are asking me here. I do have relationships built into the database. Here is the actual SQL script:

CREATE TABLE [dbo].[Country] (
	[ID] [int] NOT NULL ,
	[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Person] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MiddleName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[State_ID] [int] NULL ,
	[Country_ID] [int] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[State] (
	[ID] [int] NOT NULL ,
	[Country_ID] [int] NOT NULL ,
	[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShortName] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Country] WITH NOCHECK ADD
	CONSTRAINT [PK_Country] PRIMARY KEY  CLUSTERED
	(
		[ID]
	)  ON [PRIMARY]
GO


ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
	CONSTRAINT [Person_PK] PRIMARY KEY  CLUSTERED
	(
		[ID]
	)  ON [PRIMARY]
GO


ALTER TABLE [dbo].[State] WITH NOCHECK ADD
	CONSTRAINT [PK_State] PRIMARY KEY  CLUSTERED
	(
		[ID]
	)  ON [PRIMARY]
GO


ALTER TABLE [dbo].[Person] ADD
	CONSTRAINT [FK_Person_Country] FOREIGN KEY
	(
		[Country_ID]
	) REFERENCES [dbo].[Country] (
		[ID]
	),
	CONSTRAINT [FK_Person_State] FOREIGN KEY
	(
		[State_ID]
	) REFERENCES [dbo].[State] (
		[ID]
	)
GO


ALTER TABLE [dbo].[State] ADD
	CONSTRAINT [FK_State_Country] FOREIGN KEY
	(
		[Country_ID]
	) REFERENCES [dbo].[Country] (
		[ID]
	)
GO

Re: Class Design

Tony, Just a word that I'm currently looking at your SQL.

Re: Class Design

Having the State ID = null in the Person table is not a violation of R.I.. You do not have to have a null value in the table you reference. R.I. is for fields that have a value other than null in them that reference another table. Now if the State ID in the Person table was a not null field then it would of course always have a corresponding ID in the State table. - Eric McVicker

Re: Class Design

Tony, 1. Your R.I is OK:

ALTER TABLE [dbo].[Person] ADD
	CONSTRAINT [FK_Person_Country] FOREIGN KEY
	(
		[Country_ID]
	) REFERENCES [dbo].[Country] (
		[ID]
	),
	CONSTRAINT [FK_Person_State] FOREIGN KEY
	(
		[State_ID]
	) REFERENCES [dbo].[State] (


		[ID]
	)
GO

2. * Now see the bolded line of your table CREATE TABLE [dbo].[Person] * I was surprise when I saw you could Insert NULL for State_ID, Country_ID because I did not expect to see those 2 columns defined 'without' NOT NULL which will make a R.I more strict which exclude make State mandatory. This lead me to ask you then the question#1 which is how to include the optional State (That's the point of question#1) From my question#1, I was expected to see: [State_ID] [int] NOT NULL , [Country_ID] [int] NOT NULL But with your SQL (bolded lines) and the test I did I see this is OK. Here is how it work, if INSERT has a NON-NULL value, then internally SQLSERVER will check that value against the STATE table. If INSERT has a NULL, then it look like it will not check against the STATE table. This is what we want 'conceptually'. I was surprise by the fact that SQLSERVER did not do a check for a NULL even that STATE_ID / COUNTRY_ID was defined NULL to enforce R.i. This is the reason I kept asking you to re-read my question#1. CREATE TABLE [dbo].[Person] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MiddleName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LastName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [State_ID] [int] NULL , [Country_ID] [int] NULL ) ON [PRIMARY] GO 3. From point 1 and point 2, we're OK on Database size unless you have something to add or question to my reply. 4. Give me a signal so that we can focus on Class Design (and if there is a need to adjust database we can goback to point 3)

Re: Class Design

I'm set on the database stuff. It works the way I need it to and it holds the information I need. It is possible for a Person to have a Country w/o a State. It is not possible for a Person to have a State w/o a Country. Each State belongs to exactly 1 Country. It is possible for a Person to not have a State or a Country. Now, we are back to my original question: "What's the best way to do this?" Scenario #1:

--- Person Class
id                 int (PK)
Firstname          string
Middlename         string
Lastname           string
State_ID           int (FK)
StateName          string
StateShortname     string
Country_ID         int (FK)
CountryName        string
CountryShortname   string

Scenario #2:

--- Person Class
id                 int (PK)
Firstname          string
Middlename         string
Lastname           string
State              State object
Country            Country object

--- State Class
id                 int (PK)
Name               string
Shortname          string
Country_ID         int (FK)

--- Country Class
id                 int (PK)
Name               string
Shortname          string

Scenario #3:

--- Person Class
id                 int (PK)
Firstname          string
Middlename         string
Lastname           string
State              State object
Country            Country object

--- State Class
id                 int (PK)
Name               string
Shortname          string
Country            Country object

--- Country Class
id                 int (PK)
Name               string
Shortname          string

Re: Class Design

JOAC, ::Having the State ID = null in the Person table is not a violation of R.I.. You do not have to have a null value in the table you reference. R.I. is for fields that have a value other than null in them that reference another table. Now if the State ID in the Person table was a not null field then it would of course always have a corresponding ID in the State table. Thanks for the note. But while doing the discussion. I wanted to have all the information needed. If you look at the orginal question alone, you would *not* be able to determine what is what unless I asked for some clarification and especially when Tony told us about his requirement that State is optional. Posted: 08-05-2003 10:04 AM Now welcome to the party, see if you can propose any Class Design suggestion to Tony based upon his last post Posted: 05 Aug 2003 10:16 PM. where he re-iterate the business requirement. It's better that you have a comprehensive look. And you already did (should I safely assume that?)

Re: Class Design

Tony, JOAC Back to Database Design again. See Posted: 05 Aug 2003 08:25 PM to see Tony's TSQL. 1. Posted: 08-05-2003 10:16 PM ::It is possible for a Person to have a Country w/o a State. OK INSERT INTO PERSON VALUES ('FIRST', 'M', 'LASTNAME', NULL,1) because of [State_ID] [int] NULL 2. ::It is not possible for a Person to have a State w/o a Country. Each State belongs to exactly 1 Country. >>Your Person Table/TSQL will not respect this. INSERT INTO PERSON VALUES ('FIRST', 'M', 'LASTNAME', 1,NULL) because of [Country_ID] [int] NULL CREATE TABLE [dbo].[Person] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MiddleName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LastName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [State_ID] [int] NULL , [Country_ID] [int] NULL ) ON [PRIMARY] 3. ::It is possible for a Person to not have a State or a Country. I understand: "If a person's State is null and a person's Country is null, then error". >>Your Person Table/TSQL will not respect this. INSERT INTO PERSON VALUES ('FIRST', 'M', 'LASTNAME', NULL,NULL) because of [State_ID] [int] NULL , and [Country_ID] [int] NULL CREATE TABLE [dbo].[Person] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MiddleName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LastName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [State_ID] [int] NULL , [Country_ID] [int] NULL ) ON [PRIMARY] JOAC Do you see why I kept asking Tony question/requirement which made him designed that way. Without *his last post*. It will be impossible to have a correct database/tables. At this stage, we are still at database design step due to his latest business requirement. PS: I hope you see why I asked questions before we can attempt for a solution: Posted: 05 Aug 2003 06:11 PM -My question # 1 is concerned about designing first which focused on R.I for the optional state requirement. See Posted: 08-05-2003 11:22 AM, Posted: 08-05-2003 11:45 AM And my concern about NULL or NOT NULL for State and Country and in which case thing will not hold true. And it turned out your lasted requirement/post make some NULL case unwanted. Posted: 08-05-2003 09:24 PM From my question#1, I was expected to see: [State_ID] [int] NOT NULL , [Country_ID] [int] NOT NULL Versus [State_ID] [int] NULL , [Country_ID] [int] NULL

Re: Class Design

Tony, JOAC 1. Database designwise: This is still a pending issues from the DB design point of view. See Posted: 05 Aug 2003 11:40 PM and previous discussion related. 2. However Class designwise: If you still remember what I said and wondered "I do not know if there is an equivalent relationship between Datbase design and Class design." Posted: 04 Aug 2003 10:28 PM Now, I think the answer is 'it depends' on context. With 'this assumption of it depends', if we have a look at the 'ADDRESS' on a letter, we see: LINE1. FIRSTNAME LASTNAME LINE2. STREET NO AND STREET NAME LINE3. CITY, STATE, ZIPCODE LINE4. COUNTRY A. So for Optional State, the letter Address has no state info. LINE1. FIRSTNAME LASTNAME LINE2. STREET NO AND STREET NAME LINE3. ===> no state displayed LINE4. COUNTRY B. So for Mandatory State, the letter Address has state info. LINE1. FIRSTNAME LASTNAME LINE2. STREET NO AND STREET NAME LINE3. CITY, STATE, ZIPCODE ===> state displayed LINE4. COUNTRY C. Point A and B implies we will have 2 classes only instead of 3 classes which 'were' formerly Person, State and Country. --Person Class id int (PK) Firstname string Middlename string Lastname string Address object of Address class --Address Class State_ID int (FK) -- in case of Optional, we have no data. Like Letter address StateName string -- in case of Optional, we have no data. Like Letter address StateShortname string -- in case of Optional, we have no data. Like Letter address Country_ID int (FK) CountryName string CountryShortname string 3. Your Posted: 05 Aug 2003 10:16 PM and for your question about which class scenario? My point 2.C is the same as your scenario #1 except we group state and country info. into Address Class. So I'll go with your scenario#1 or you can do as my point 2.C which resembles a letter address by analogy. 4. If you or any one else have a better Class design, please propose or share your experience. 1M thanks.

Re: Class Design

I do have fields in the DB and class to handle address fields. I'm not requiring any of the address lines, that will be filled in later. I'm more interested in capturing where people are from in the City, State, and Country fields. The address won't be used much, but the location (city, state, country) will be used alot.