All geek questions in one place

Database Schema Support (Platform Independent)

I have a kind of opinion question, but at the same time one that may have the right answer. I am trying to develop a product suite and want to make sure that since I do it myself, I am doing it right the first time. I rewrote the diagram several times, each time thinking it was better. Then I could find some new idea, and it will require a lot of work on the circuit, or it will break my circuit.

In college, I learned about the “rationalization” (I think the word they used could be far away) databases and there are 5 levels. From what I remember, level 3 was the most common. I know that the practice was to make sure that the data was not repeated, and for this you had to split the tables into smaller tables. And depending on how far you smash it, the higher the level. Well, I don’t know if I want the highest level, but I know that I want it to be as effective as I can get it. I had 4 years SQL Server 2000/2005/2008 and 2 years Oracle, about 6 months with Informix (5+ years ago), a touch here or there with mySQL and about 6 months of access. My preference is SQL Server, but I would like the schema to be just as efficient on both platforms.

Here is the psuedo layout diagram of some tables, then I will explain what I want to do.

Manufacturers
  ManufacturerID (Identity)
  ManufacturerName
  ManufacturerStreetAddress
  ManufacturerZipCodeID
  ...

ZipCodes
  ZipCodeID (Identity)
  ZipCode
  ZipCodeStateID
  ...

States
  StateID (Identity)
  StateName
  StateAbbreviation
  ...

Cities
  CityID (Identity)
  CityName
  CityStateID
  ...

, psuedo, , , , , . , , , . , , -, . Zip-Code "", . . , StateID ZipCodes Cities . , . , CityNames, CityStates (CityNameID StateID). , , , , , . , , , , , , - .

:

  • psuedo, , ()?
  • "" - ( )? ()
  • "" , (, ..), psuedo, , ( )?

, , . , . , , , . .

: , , , "" , . LEFT OUTER JOINs, , , , 4 ? .

: , , , , , psuedo?

Manufacturers
  ManufacturerID (Identity)
  ManufacturerName
  ManufacturerStreetAddress
  ManufacturerCCSZID --CCSZ (Country, City, State, Zip), needs a better name
  ...

ZipCodes
  ZipCodeID (Identity)
  ZipCode
  ...

States
  StateID (Identity)
  StateName
  StateAbbreviation
  ...

Cities
  CityID (Identity)
  CityName
  ...

Countries
  CountryID (Identity)
  CountryName
  CountryAbbreviation
  ...

CountryCityStateZipCodes
  CountryCityStateZipCodeID (Identity)
  CCSZCountryID
  CCSZStateID
  CCSZCityID
  CCSZZipCodeID

, :

SELECT  M.ManufacturerStreetAddress,
        CN.CountryName,
        CN.CountryAbbreviation,
        S.StateName,
        S.StateAbbreviation,
        C.CityName,
        Z.ZipCode
FROM Manufacturers M
LEFT OUTER JOIN CountryCityStateZipCodes CCSZ ON CCSZ.CountryCityStateZipCodeID = M.ManufacturerCCSZID
LEFT OUTER JOIN Countries CN ON CN.CountryID = CCSZ.CCSZCountryID
LEFT OUTER JOIN States S ON S.StateID = CCSZ.CCSZStateID
LEFT OUTER JOIN Cities C ON C.CityID = CCSZ.CCSZCityID
LEFT OUTER JOIN ZipCodes Z ON Z.ZipCodeID = CCSZ.CCSZZipCodeID

, , , , , . , , ?

+3
3

, "", .

- , . , , :

  • - 0, , a .
  • , ZIP- . , , . .

, , "". , INNER JOIN - "" , "ManufacturerZipCodeID" , LEFT JOIN, .

+3

, . - , , , , .

, , , , , , . , "NY" "ny" "Ny" "New York" "NewYork". , , .

+1

, , , - . . , :

  • .
  • Zip-
  • .

uniques. , :

STATE
---
State ID (PK)
State Name

ZIP
---
Zip ID (PK)
Zip Code (NK)

. Zip, ? , Zip City. ? City, № 3 , . ZIP. ZIP-:

ZIP
---
Zip ID (PK)
Zip Code (NK)
City ID (FK)

, "" Zip City, City. . ( " " ) . , City:

CITY
----
City ID (PK)
City Name

. , ? . , № 4 . . , City:

CITY
---
City ID (PK)
City Name
State ID (FK)

. , Zip (. Zip), (. City).

, "" . . , " " " ". "" , , . " ", ​​ :) , . , .

, , , .. , .

+1
source

Source: https://habr.com/ru/post/1762457/

More articles:


All Articles