April 19, 2009 at 7:51 pm
I have pick list table. This is referenced by other tables via FK. For example
CREATE TABLE PICK_LIST
(
item_id int NOT NULL IDENTITY(1,1),
item_code varchar(20) NOT NULL,
item_type varchar(20) NOT NULL,
item_desc nvarchar(200) NOT NULL
)
GO
values could be
item_id item_code item_type item_desc
----------- -------------------- -------------------- --------------------------------------------1 HH CAR_TYPE HONDA
2 TY CAR_TYPE TOYOTA
3 BM CAR_TYPE BMW
4 CON HOUSE_TYPE CONDO
5 SFR HOUSE_TYPE SINGLE FAMILY HOME
Hence we have 3 car types item_ids 1,2,3 and 2 house types item_ids 4,5
I want to create a person table
CREATE TABLE PERSON
(
person_id int not null identity(1,1),
car_id int,
house_id int
)
I will create a FK on car_type_id and house_type_id with pick_list.item_id but how can specify that I only want an item_id for a certain item_Type. For example, car_id should have an item_type_id of ONLY CAR_TYPE. Hence item_id 4 is invalid for a car type since it is an item of type house.
Any suggestions? Trigger I can implement or from stored proc i can implement the check. looking for ideas to implement in the schema in someway.
April 20, 2009 at 5:22 am
My first suggestion is to create separate type tables. Yes, you can maintain a list of different types in one table, and you can create triggers to validate the chosen types, but that to me seems to be more hassle than maintaining several type tables.
April 20, 2009 at 8:16 am
Sanjay, the simplest approach without separating tables by type is to isolate your call in a stored procedure and have your top-level interface call it with a parameter for type. That way you have a fair degree of control at the interface level, and your procedure won't contain any hard-coded type management.
Having said that, if the pick list table is potentially going to get large, you may want to consider separation, as it would probably be more efficient in that case. If small though, that parameter-based calling approach does work well.
April 20, 2009 at 11:46 am
I guess I will seperate the tables. One of the reasons for keeping the same table was that the data needs to be translated into multiple languages at display time. I was planning to have 1 table and provide a module to translate anything in that list. I guess I will refactor to have multiple physical tables and then build 1 common logical business object with a combination of data from these table. Thanks for the inputs though, just wanting to make sure I did not miss any feature in SQL that allowed us to do this. I am using SQL 2008, am going to play with something around hierarchy_id to see if I can create a link somehow in schema and enforce RI.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply