MySQL – How to Model a Database with Multiple Options

database-designmariadbmariadb-10.4MySQLoptimization

I apologize if this might be a rather beginner-level-question.
For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB.

The basic idea:

I have a Table called Article in which I want to store simple fields such as name (e.g. varchar), description (e.g. varchar), …, etc. also more complex information like Producer, Price and so on.

I have worked out the following:

Implementation I (using just a FK)

Table: Article
| id (auto_inkr.) | fk_producer | ... |
| --------------- | ----------- | --- |
| 1               | 1           | ... |
| 2               | 1           | ... |
| 3               | 2           | ... |
| ...             | ...         | ... |

As defined, Producer(id: 1), is the Producer of Articles (1, 2, ...?).

Implementation II (using a lookup table)

Table: Article
| id (auto_inkr.)   | fk_article_producer | ... |
| ----------------- | ------------------- | --- |
| 1                 | 1                   | ... |
| 2                 | 1                   | ... |
| 3                 | 2                   | ... |
| ...               | ...                 | ... |

Table: Article Producer
| id (auto_inkr.)   | article_fk  | producer_fk |
| ----------------- | ----------- | ----------- |
| 1                 | 1           | 1           |
| 2                 | 2           | 1           |
| 3                 | 3           | 2           |
| ...               | ...         | ...         |

In this case here, I have implemented a mapping table. This would also allow me to have a many-to-many configuration. I don't have to implement it that way – it is up to my backend-code how I store the data.

Questions

Please keep in mind, that my Article table as of now will have like 7+ dependent tables (as shown in the example here: e.g. Producer). I want to design it, so I can write an efficient filter-function (query) for my text- respectively tag-based-search later on.

Which design should I choose and why?
What are the benefits or downsides of these approaches (best practice)?

Best Answer

Design 2 two is needed when here are multiple producers per article, you need a bridge table between article and producer, so a producer can have multiple articles and an article can have multiple producers

your first is only valid for 1 to 1 reöationships.

so what you you t have 1:1 or m:n

Thet will determine, what you need

Related Solutions

Mysql – how to model this in database

City
----
CityID
CityName
PRIMARY KEY (CityID)

Hotel
-----
HotelID
HotelName
CityID
PRIMARY KEY (HotelID)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)

and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

and default Package Version:

PackageDefaultVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.