Sticky design issue in trying to avoid duplication

Good day.

I would really appreciate it if someone could point me in the right direction here. It's been a while since I've worked with databases and to top that off, I don't think I've ever come across a problem quite like the one I'm facing here.

I'm busy developing a database for a website which will contain mostly information on its users. It will be something similar to a job placement site/database.

The problem I have run into while doing normalisation, is that there are different categories of users that will need different information stored for each. This in itself is not a problem, but the fact that certain information is required for all categories and certain info only for some, is.

I don't know if I'm describing this properly - I will try to clarify with an example:

Say for instance you would have dancers and also models. Both of them would need to have data such as 'Height' and 'Weight' stored. Now, normally, one would simply put that in a 'Person'-type table, but this information isn't necessarily required for all other types of users (or 'Persons'), such as voice actors, in which case, one would have to store null values (which I don't want to do).

So there are some fields which need to be in multiple tables (Dancers, Models, etc.) and some fields which need to be in only certain singular tables (such as e.g. 'Instrument' for a musician).

How can I ensure data integrity and avoid duplication of data while still maintaining a properly normalised database? Is there something really basic that I am just overlooking here, or is this problem really this confusing?

I would greatly appreciate any advice in this regard as I'd like to get this project off the ground.

Thanks in advance!

You can create a table with common attributes for all, then create additional tables with specific attributes with a foreign key pointing to the common attributes table.

Quote:

---

Originally Posted by skhanal

You can create a table with common attributes for all, then create additional tables with specific attributes with a foreign key pointing to the common attributes table.

---

Okay, fair enough, that does make sense, but I'm still a little concerned...

See the problem is that there are hardly any attributes that are shared across the board. Some groups may share some attributes and differ on others, while other groups may have their own unique set of shared attributes and unique attributes.

So doing it the way you suggest, doesn't that open the database up to a lot of duplication problems? For instance, if someone signs up and selects two different 'occupations' in their profile, how do I prevent duplicate data being stored for each of the occupations, considering the fact that it is the same user?

How is this.

If you want any attribute such as Nationaly to be added to any Details, simply link them. If you want to remove the Nationality for specific Details, delete the relationship.

Attachment 507