SQL Server Database Design – Too Many Entities vs Too Many Tables

The immediate question is "what do you mean by 'user defined' fields?" Who defines these? For purpose of this I am assuming that these are fixed for each table type by a small number of users (administrators) who have special access.

One thing I have done in the past is to have separate UDF tables for each entity type, but instead of separate rows per value, allow stored procedures to alter the columns on those UDF tables, so you can essentially join straight across, 1:1. This approach I call "semi-EAV." I think have catalogs like your user_defined_fields table to help the app generate queries.

Of the methods you are discussing, I would suggest using XML if my semi-EAV approach is not sufficient. The tradeoff here is that semi-EAV allows you to do more rigorous schema validation using the tools you are familiar with in SQL. The advantage of XML is that you can have a great deal of additional flexibility that this approach doesn't allow.

As a note, with LedgerSMB, we are talking about moving from semi-EAV for custom values to JSON, but we are on PostgreSQL. XML might have been an option as well.

I think you can, using a "diamond" relationship diagram:

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;