Create a composite attribute in My sql ER diagram?

Question

I am currently working on a project in mySQL and I was wondering if mySQL makes it possible to create a set of composite attributes for an attribute of an entity. The attribute is not the primary key, it is an address so the composite attributes are supposed to be, street, town, state ECT... A) is this possible? if so which type would I use/ how would I go about doing it. B) is this practical I have about 10 entities and I feel like a couple of them will need composite attributes that was just the best example.

Solution

No, MySQL doesn't support "composite" attributes.

You could "break out" the components of the "composite" attribute into a separate table. Or, you can keep them as individual columns in the same table.

I use the column name to "identify" the attributes that make up a composite attribute. You gave a good example with "address". It really is one attribute, with several components.

Here's a (neutered) example of one of my table definitions:

CREATE TABLE `foo` (
`id`              COMMENT 'PK'
`category_id`     COMMENT 'FK to ...'
`display_name`
...
`address_street`  
`address_street2` 
`address_city`    
`address_state`   
`address_country` 
`address_postal_code`
...
`country_code`    COMMENT 'ISO 3166-1-alpha-2 code'
`latitude`     
`longitude`    
...

Bottom line, MySQL doesn't support composite attributes. But I can use a naming convention to somewhat "relate" the components together.