July 16, 2014 at 12:48 pm
Any advice on the best ways to approach the following scenario?
- A table consisting of customers; one row for each customer, and includes that customer's type of house, car, insurance, and type of internet connection.
- The following tables:
- Table A - Types of Houses
- Table B - Types of Cars
- Table C - Types of Insurance
- Table D - Types of Internet Connections
The data in tables A-D does not directly relate to the customer table, but the data in these tables will drive how the customer is classified.
The design would need to be flexible enough to handle the following type of scenarios:
- If a customer has a house of type 'Single Story' (Table A), and a type of car = 'Ford' or 'Chevrolet' (Table B), classify the customer as type '123'.
- If a customer has insurance of type 'Life' (Table C), House = '2 story' or House = 'Single Story', and Car = 'Honda', classify the customer as type '345'.
These are just a couple examples. Any suggestions on how to design table structures to support this type of associations?
Thanks.
July 16, 2014 at 1:54 pm
I would put foreign keys to table A-D into the customer table.
Join all tables together and then a giant case statement that calculates the custome type.
Or are the business rules "dynamic" and stored inside a table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 16, 2014 at 2:02 pm
Design has always been interesting.
My suggestion is to use integer primary keys with identity property.
Example:
create table dbo.HouseTypes(
Id int identity(1,1),
[Description] varchar(500)
--add more columns by require
)
alter table HouseTypes
add constraint [PK_HouseTypes] primary key(Id)
--create the other tables: CarTypes, InsurenceTypes, InetConnectionTypes ...
create table dbo.House(
HouseId int identity(1,1),
HouseTypeId int,
[Address] varchar(200)
--add more columns
)
alter table dbo.House
add constraint [PK_HouseId] primary key(HouseId)
alter table dbo.House
add constraint [FK_HouseTypes_HouseType_Id] foreign key(HouseTypeId) references dbo.HouseTypes(Id)
--Create the other tables: Cars, Insurence, InetConnection ...
--or alter if already Customer exists, so that it has FKs from above tables
create table dbo.Customer(
CustomerId int identity(1,1),
HouseId int,
CarId int,
InsurenceId int,
InetConnectionId int
--add more columns
)
alter table dbo.Customer
add constraint [PK_CustomerId] primary key(CustomerId)
alter table dbo.Customer
add constraint [FK_House_HouseId_HouseId] foreign key(HouseId) references dbo.House(HouseId)
--add the other constraints
Create fast-narrow-static indexes on the FK columns.
With this design you can join all the tables on the primary/foreign keys, and no need for introducing extra indexes. I've been working on systems massively using int primary keys with identity property, and no performance issues.
Igor Micev,My blog: www.igormicev.com
July 16, 2014 at 4:53 pm
A table consisting of customers; one row for each customer, and includes that customer's type of house, car, insurance, and type of internet connection.
I think you need to step back and review that. I see no reason why a customer couldn't have multiple cars, have multiple houses and/or have multiple internet connections, each of a different type.
By "house" do you mean "primary residence"?
Is a customer required to have a "house" to be in the table? What about someone who rents an apartment, say? Likewise, is car required or optional?
Edit: Btw, one other thing you really must do if you want to end up with a really good design: do a logical design first, then a physical design later. Logical design does not have "tables" or "indexes", just the business data and relationships. Properly model that first, without worrying at all about physical structures. That's the critical and unique part of the process -- converting the logical to a physical structure is a more generic process.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply