How do I model content (types) from a database-centric point of view?

Question

How can I model content from a "database-centric" point of view?

For example to show Appartments in a District in a City, as in this example:

Table Cities:
CityID (int) primary key
City (string)

Table District:
DistrictID primary key
CityID foreign key
District (string)

Table Appartment:
AppartmentID primary
DistrictID foreign
AppartmentName
NrOfRooms
.....etc....

How would I create content types (?) to do this?

Answer 1

I'm assuming this question is about Drupal 7 ... Only using Site Building features (no custom module development yet) should get you pretty far (if not far enough ...).

The Relativity Data Model for Drupal

Have a look at the (illuminating) article "Relativity Model for Drupal", for which this is an introduction to it (quoted from this linked page):

The Relativity Data Model for Drupal is a zero-custom-code, collection of steps, procedures and methods that strive to help site builders, users and site visitors interact with Drupal in a far easier and more efficient way. This is done by:

• Data architecture and data modeling merging as a single step through a documentation procedure.
• Customized user interfaces that cater to individual workflows throughout permission roles.
• Multi-level relational data structures that relate to other multi-level relational structures.
• Mimicking the concept of database joins within Drupal's UI's.
• Vastly reducing the size of Drupal sites by a small handful of modules that provide greater power, speed and expansion capabilities.

The small handful of modules (cfr. the last bullet) is like so (apart from Drupal core):

• https://www.drupal.org/project/ctools
• https://www.drupal.org/project/date
• https://www.drupal.org/project/entity
• https://www.drupal.org/project/entityreference
• https://www.drupal.org/project/entityconnect
• https://www.drupal.org/project/field_group
• https://www.drupal.org/project/token
• https://www.drupal.org/project/pathauto
• https://www.drupal.org/project/views

This same topic is also presented in the video "Revolutionary DRUPAL Data Modeling Concept!! (This will change your Drupal life!)".

Typical add-on modules

Even though that small handful of modules above can get you already extremely far, I often add a subset of other contributed modules to it also, as further detailed below.

Tables, Columns and Foreign keys

• Tables: Use the Entity Construction Kit (ECK) module to create 'entities' (= fieldable things ...). In your case that would be entities like Cities, District and Appartment.
• Columns: For each of those entities use "fields" for each of the columns you mentioned. Should you run into fields such as dates, you would have to add modules such as date also.
• Foreign keys: For the "foreign keys" use the Entity reference module (possibly some related modules of it also, extending its functionality, as mentioned on the ECK project page) and/or the References module (cfr. 'node_reference' and 'user_reference' as contained in CCK for D6).

Business logic

Using the Rules and Flag module, it is possible to implement all sorts of business logic (= rules), simply using the typical Drupal admin UI for those modules.

Queries, custom reports and charts

• To perform queries (listings, etc), use the views module, possibly further extended with modules such as Views Slideshow: Galleria (there are dozens of modules to further enhance views).
• The Forena module can be used as a supplement / alternative (e.g. if you'd need to also create charts or so, or if you have a need to access data in some external database such as MS SQL, Oracle, etc).
• Create charts using the views integration of the Charts module to visualize a lot of data in a way that can be quickly digested.

Page Layout

Modules like Display Suite and/or Panels can help to enhance the look and feel of it all also.

Many more modules

• Group (a pretty new alternative to Organic groups, to create arbitrary collections of your content and users and grant access control permissions on them).
• Workflow (to create arbitrary workflows, and assign them to entities).
• IP Geolocation Views & Maps, a mapping engine to create Views-based mapping solutions using the map renderer of your choice (Google, OpenLayers or Leaflet)
• Or, or ... on and on goes the list.

Disclosure: I'm a (co-)maintainer of Forena and Charts.