# Design: How do you decide when items are related vs when they are attributes?

I've been trying to figure this out for a while. Let's say you have a person, for example. An attribute of a person is his or her social security number, right? But a person also has a social security number. So, in an ER diagram, you could draw a square box for person and a square box for ssid, and you could connect them by a diamond, has. Alternatively, you could draw a circle, ssid, and you could connect it to a square person box.

This isn't just true of ssid, though. It's possible to draw either-or with concrete things, like a car, a pet, or a phone number, or with conceptual things, like a phone number, a friendship, or a mood. So, where do I draw the lines? Is the object of design just to get everything down on paper and looking nice, or should I be using a guide?

-

There are two ways to look at this, the informal way and the formal, theoretical way.

The Informal Way:

If something is of so much interest to your system that you want to record attributes about it, then it probably belongs in its own table.

If the only thing you care about Social Security Number is what digits it contains (i.e. what the number is) then this number is best modelled as an attribute of something else (e.g. PERSON).

If, on the other hand, your system cares about other facts that pertain to SSID, like maybe when it was issued, which office issued it and whatever else you might know and care about an SSID, then you probably want to split everything you know about the SSID into its own table and then relate that table to PERSON.

The Formal Way:

If the rules of normalization demand that you split your multiple facts (attributes) about an SSID into a separate relation (table) - of which the social security number is the primary key, then SSID is a box and not a circle.

Specifically, if you have attributes which depend solely on the SSID then you would want to remove these from a PERSON table because they would have only a transitive dependency on the ID of PERSON and therefore 3NF demands that these attributes be removed to a separate table.

-
+1 for description of transitive dependencies and mentioning that they should be split into their own table. – Aaron Jan 27 '12 at 13:38
@Joel I kind of see where you're coming from, but it seems like many of my tables have some inexclusive attributes and only one unique attribute. For example, I may have 10 tables with only date issued, type, value, and maybe id. – Wolfpack'08 Jan 27 '12 at 13:49
@Wolfpack'08 You shouldn't be concerned if you have an attribute with the same name on many different tables. A date_issued on a drivers license is not the same as a date_issued on a parking ticket. Lots of objects will have similar types of information recorded about them. What puts them in their own table is if the facts also have sub-facts, if you like. The point of normalization is to group your facts (columns) into relations (tables) such that each fact is functionally dependent on "The key, the whole key, and nothing but the key, so help me Codd." as the saying goes. – Joel Brown Jan 27 '12 at 14:06

One rule of thumb is that if it is 'physical' (concrete as opposed to abstract) then it is an entity otherwise it is an attribute.

Another rule of thumb in ER modelling is that an table EITHER models an entity OR a relationship between entities but never both. There can be other kinds of table, of course e.g. it is sometimes conducive to put all attributes of the same type (e.g. colors) in a table, known as a lookup table (i.e. is neither an entity table nor a relationship table but is still valid and useful).

-
What do you mean by "physical"? Do you mean physical as in concrete, as opposed to abstract? Also, thank you for having a go at the answer. I appreciate it a lot. – Wolfpack'08 Jan 27 '12 at 12:44
@Wolfpack'08: I agree your wording :) – onedaywhen Jan 27 '12 at 14:01
thank you for the update. – Wolfpack'08 Jan 28 '12 at 10:36