Intelligence Database Design

Help w/ database structure

[jribbe101]

Greetings!

I'm starting up a new company and need some help with the way we are designing the database. In the SQL database design, is it possible to have multiple amounts of data in one field? For example: In a CustState field, have TX, LA, MS, AR. Basically, to have multiple cities assigned to one customer without having to create seperate columns for each individual state they are signed up to. I hope this makes sense, thanks for the help!

[mattur]

It's a Many-to-Many relationship. You can store multiple atomic pieces of data in one field, but it's sub-optimal, and limits your future options (i.e. you can't join on the field). Standard, relational solution:

tblCustomer:
------------
CustID
etc

tblState:
---------
StateID
etc

tblCustomer_State:
------------------
CustID
StateID

So a customer with 3 states will have 3 records in tblCustomer_State. Look up "database normalization" for more information. HTH

[JGWhitelaw]

You need to set up a seperate table for the state, and have a related field in the first table which chooses from the second table.

[mattur]

JG: that is the correct method for a 1:Many relationship i.e. each customer only has one state but a state can have multiple customers. The OP mentions each customer having multiple states, a Many:Many relationship which requires a third table.

[rocknbil]

Awesome discussion, one that I always question of myself: Is this the best way to set up this database?

Generally I try to start with "what's fastest?" Inherently numeric data will always be fastest to search, insert, add, delete, and also provides the maximum flexibility for future modifications, but there comes a point where the complexity of joining tables offsets any speed you gain.

Is this an errant assumption?

Using this case as an example,

In a CustState field, have TX, LA, MS, AR.

I would do this:

customers 
rec_id ¦ cust_id ¦ ........ 
123 ¦ 127 ¦ ..... 

states 
id ¦ abbr ¦ full ..... 
1 ¦ AL ¦Alabama ...... 
2 ¦ AK ¦Alaska ...... 

cust_states 
id ¦ cust_id ..... 
1 ¦ 127 ¦ ...... 
2 ¦ 127 ¦ ...... 

You could then do this (one of many) query which would return the requested state list:

select  
 customers.cust_id, states.abbr,states.full 
 from customers  
 left join cust_states on cust_states.cust_id=customers.cust_id 
 left join states on states.id=cust_states.state_id 
 and cust_states.state_id <> ''; 

cust_id ¦ abbr¦ full 
127 ¦ AL ¦ Alabama 
127 ¦ AK ¦ Alaska 

. . . All searching on integer fields. Obviously the two extra tables could be used to query the customer states at any time in searches related to customers, or the states could be queries at any time in searches unrelated to customers, expanding the flexibility. But it may make for more complex queries in general use.

At what point do complex joins become less efficient than a table with limited flexibility but easier select statements?

[jribbe101]

guys, thanks for the input. These are great opinions. I may have another question about this database design.

Thanks.