Data Modeling Help - Do I add another table, change existing table's usage, or something else?

Assume I have the following tables and relationships:

Person
- Id (PK)
- Name

A Person can have 0 or more pets:

Pet
- Id (PK)
- PersonId (FK)
- Name

A person can have 0 or more attributes (e.g. age, height, weight):

PersonAttribute
_ Id (PK)
- PersonId (FK)
- Name
- Value

PROBLEM: I need to represent pet attributes, too. As it turns out, these pet attributes are, in most cases, identical to the attributes of a person (e.g. a pet can have an age, height, and weight too). How do I represent pet attributes?

1. Do I create a PetAttribute table?

   PetAttribute

   • Id (PK)
   • PetId (FK)
   • Name
   • V开发者_Go百科alue

2. Do I change PersonAttribute to GenericAttribute and have 2 foreign keys in it - one connecting to Person, the other connecting to Pet?

   GenericAttribute

   • Id (PK)
   • PersonId (FK)
   • PetId (FK)
   • Name
   • Value

   NOTE: if PersonId is set, then PetId is not set. If PetId is set, PersonId is not set.

3. Do something else?

Actually, you should delete the PersonAttribute table and just add the columns (age, height, weight, etc.) to the Person table.

Add similar columns to the Pet table. This avoids a common beginner mistake called an "Entity Attribute Value Table". See "Five Simple Database Design Errors You Should Avoid", for example.

The only time you should put an "attribute", like weight, in a separate table is when the owner can have more than one of that exact attribute. Then the table should be specific, "WeightHistory", if it's a list of weights over time, etc.

You could do 1. but as your attributes are not fixed, they are just name value pairs this seems like a lot of duplication.

2 seems like a bad idea, as it is awkward to enforce the integrity with this sort of set up.

for 3, could you have a link table in the middle for an attribute group, which contains the id of the group and the ids of the individual GenericAttributes, then you have the AttributeGroupId as a property of the person and of the pet?

Table AttributeGroup
- Id (PK)
- GenericAttributeId  (PK,FK)

Table GenericAttribute
- Id (PK)
- Name
- Value

Person
- Id (PK)
- Name
- AttributeGroupId

Pet
- Id (PK)
- Name
- AttributeGroupId

so then the AttributeGroupId identifies a collection of GenericAttribute instances which are associated with the Pet or the Person. This also means that if you need to attribute something else in the future you just need to add the AttributeGroupId to that thing.

EDIT:

You really need to consider why you are doing this though. Are your attributes really dynamic, as this is the only reason I can think of that you would want this model? ie are your users defining the attributes in the application, can they add any attribute they want to a Person or a Pet? If not, if the application is in control of the attributes then you should consider Brock's advice. If the users are entering the attributes and their values themselves then you may need a model like this but it makes your queries much more complicated. If you can avoid it then its probably right to do so. I gave this as an example of a solution to your question, I can't suggest that it is a good idea without knowing more about your specific use case.

EDIT2:

As you have a fixed set of attributes that both a Pet and a Person can have you could have:

Attributes
- Id (PK)
- Attribute1
- Attribute2
...
- AttributeN

Person
- Id (PK)
- Name
- AttributesId (FK)

Pet
- Id (PK)
- Name
- AttributesId (FK)

Which avoids the duplication in all of the attributes if they are numerous and allows fairly simple querying, integrity checking, and strong typing of the data.

I think that as you model PetAttribute and PersonAttribute deeper, you are going to find that are specializations of a more generic feature perhaps called PersonOrPetAttribute.

If it were up to me, I'd simply keep the two tables separate, until the absence of a table for PersonOrPetAttribute started to make queries more difficult.

If you see it differenlty, then you should probably do a google search on "generalization specialization relational modeling". This will find several good articles for you on the subject of modeling the gen-spec pattern with tables. The articles will tell you more about how to do this than I'm going to put in this response.