Is this schema screwed up, or is it just me?
\r\nvendornum varchar(14)
\r\ncountycode varchar(4)
\r\ntemplate bigint(20) (This defines which service is offered.)
\r\n\r\nWe are also interested in the state. The last two characters of the countycode are the state, so it can be derived from that or listed as its own field. Here's the current schema:
\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n
| coverage_service | ||
|---|---|---|
| idnum | bigint(20) | auto_increment |
| county_idnum | bigint(20) | |
| state_idnum | bigint(20) | |
| template | bigint(20) | |
\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n
| coverage_state | ||
|---|---|---|
| idnum | bigint(20) | auto_increment |
| vendornum | varchar(14) | |
| state | char(2) | |
\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n
| coverage_county | ||
|---|---|---|
| idnum | bigint(20) | auto_increment |
| state_idnum | bigint(20) | |
| county_code | char(4) | |
\r\n\r\nSo in order to determine what vendors offer service in what counties, I have to join three tables:
\r\n\r\n
SELECT \r\n coverage_state.vendornum , \r\n coverage_county.countycode , \r\n coverage_service.template\r\nFROM
\r\n coverage_service ,\r\n coverage_state ,\r\n coverage_county\r\nWHERE\r\n coverage_service.county_idnum = coverage_county.idnum\r\n AND coverage_service.state_idnum = coverage_state.idnum\r\n AND coverage_county.countycode = '$countycode'
\r\n\r\nIf you're wondering why state_idnum is in coverage_county, I can't tell you. But whenever we add/delete/update coverage we have to keep everything in sync manually, because MySQL (as of the version this was written for) doesn't do triggers or foreign key constraints.
\r\n\r\nI'm planning to change the schema to this:
\r\n\r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n
| coverage_service | ||
|---|---|---|
| idnum | bigint(20) | auto_increment |
| vendornum | varchar(14) | |
| countycode | char(4) | |
| state | char(2) | |
| template | bigint(20) | |
\r\n\r\nThen my query becomes:
\r\n\r\n
SELECT \r\n vendornum , \r\n countycode , \r\n template\r\nFROM\r\n coverage_service\r\nWHERE\r\n countycode = '$countycode'
\r\n\r\nI might still keep the coverage_state table, as it provides a quick lookup of what vendors cover what states, but I could also do:
\r\n\r\n
SELECT DISTINCT\r\n vendornum\r\nFROM\r\n coverage_service\r\nWHERE\r\n state = '$state'
\r\n\r\nI believe integer indexes are slightly faster to look up, but since this new schema would eliminate two joins I suspect it will be faster. Other than possible speed of the lookups, is there any reason to prefer the current schema to what I'm proposing?
