The only thing I'll add is that I find it easier to split later than combine later. Depending on what type of information you are looking at about Boats or Cars and how many points you care about and what you are doing with the data (To @JackDouglas's point about needing more specifics to give specific advice) maybe you won't need them combined, maybe you will. If you aren't sure and can design with them combined and are leaning that way, it is worth a try and testing knowing that someday you may have to split them out. Won't make refactoring easy, but IMO it would be easi*er* than trying to combine the data, if only by a little.
First of all, mysql (and other sql databases) are RDMS meaning that they are based in the relational model. This means that the design should be about entities and their relations. In your case:
Entities: locations, types.
Relation: one location can be of one type (if I have understand you correctly). This is a one-to-one relation.
The best way to store this relation is as you propose at the begining:
create table tbl_locationcollections
(
id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
name varchar(100) not null,
cleanname varchar(100) not null,
typeid int(11) not null (foreign key)
)
create tbl_locationcollectiontypes
(
id int(11) PRIMARY KEY IDENTITY, --(Primary Key)
type varchar(100)
)
but then you propose something strange, some tables to relate each type with their locations. There is no need for this table. The relation is already set by the foreign key constraint. If you want to get all the locations of the same type, for example country, you will do it in a query:
SELECT *
FROM tbl_locationcollections
INNER JOIN tbl_locationcollectiontypes
ON typeid.tbl_locationcollections = id.tbl_locationcollectiontypes
WHERE type.tbl_locationcollectiontypes = 'country'
Benefits of this aproach:
-Readability
-Scalable: you can add more types with out changing the database design
-Logic: you work with relations between entities, wich is the same abstraction our brain makes.
Hope it helps ;)
If you want to know how to store one-to-many or many-to-many relations that is another topic, feel free to ask!
Best Answer
Yes, there are some issues with this design. The pattern is Entity-Attribute-Value at least for the EntityChange table. See the link for all the reasons why this almost always should be avoided.
The Entity table itself may not be EAV, but it does nothing but increase complexity without adding any value. To know if a Contact has another tenant, another version, another draft, or is logically deleted you have to join. Even if none of these are applicable to the Contact table, your design dictates their existence and accommodation.
If you decide that Contacts can be logically deleted then add a deleted column to the contact table. If you decide you need different drafts of Contacts, then add a drafts column, etc. Essentially, there is no advantage to moving these attributes to a common table. It increases storage requirements, reduces performance more than it helps, and make the system more complex.