How to create a proper database structure for domains and subdomains?

NoGapps Published at Dev

NoGapps

I have a list of domains and list of subdomains. For example:

domain A has subdomains a1, a2, a3.
domain B has subdomain b1, b2, b3.
domain C has no subdomains

I need to create database structure so I can order it like so:

A
a1
a2
a3
B
b1
C

And I need to see clearly which one is domain or subdomain of what domain.

I tried to put domains in one table and subdomains in other table with ForeignKey to domain.

class Domain(Base):
    __tablename__ = "domains"
    domain_id = Column(Integer(), primary_key=True)
    name = Column(String(), unique=True)


class Subdomain(Base):
    __tablename__ = "subdomains"
    subdomain_id = Column(Integer(), primary_key=True)
    name = Column(String(), unique=True)
    domain_id = Column(Integer, ForeignKey('domains.domain_id'))

I'm using domains and subdomains from both of this table to crate third table:

class Title(Base):
    __tablename__ = "titles"
    title_id = Column(Integer(), primary_key=True)
    domain_id = Column(Integer, ForeignKey('domains.domain_id'))
    subdomain_id = Column(Integer, ForeignKey('subdomains.subdomain_id')) -- NULL if that is a Domain
    title = Column(String())
    status = Column(Integer())

Thorsten Kettner

You are asking for a datamodel and a SQL query.

You can use two tables:

domain (domain_id, name)
subdomain (subdomain_id, name, domain_id)

with this query:

select name
from
(
  select name, name as domain_name, 'domain' as type
  from domain
  union all
  select sd.name, d.name as domain_name, 'subdomain' as type
  from domain d
  join subdomain sd on sd.domain_id = d.domain_id
)
order by domain_name, type, name;

Or just one table:

domain (domain_id, name, parent_domain_id)

with this query:

select name
from
(
  select name, name as domain_name, 'domain' as type
  from domain
  where parent_domain_id is null
  union all
  select sd.name, d.name as domain_name, 'subdomain' as type
  from domain d
  join domain sd on sd.parent_domain_id = d.domain_id
)
order by domain_name, type, name;

Collected from the Internet

Please contact [email protected] to delete if infringement.