Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits. My question is I have a set of people for which I want to record needs mapped to importance. Needs should be taken from a set to avoid duplicates creeping in. Each person will take a subset of these and grade their importance. i.e. needs =[tea, biscuits, toilet facilities, internet access] People = [Phil, Bryan]

Phil needs tea and biscuits, tea with an importance of 10 biscuits with an importance of 3.

Bryan needs internet access and biscuits with importance of 4 and 7 respectively.

How would you suggest organizing the tables to implement this. Thanks

share|improve this question

2 Answers

up vote 4 down vote accepted

What you need is a design like this:

ERD

Keep you list of people in one table. Then keep another table with the list of things that various people might need. Normalizing people and needed things is important for being able to write simple, reliable queries about your data from the perspective of either an individual person or a particular needed thing.

Use a third table to keep track of who needs what, and how badly they need it.

share|improve this answer
Is "ID" just shorthand or do you advocate that as the actual column name in those tables? – ErikE Aug 13 '12 at 6:21
1  
+1 but i have some remarks on your naming: i would call the entity "thing" and not "needed thing". that a thing is needed is expressed by the relation "need rank". I would express the relation by a verb, so i would name the relation "needs". As far as I know the word "rank" has a special meaning (the possition number in a sorted list of items). In this situation the word "weight" used by the OP seems appropriate to me. – miracle173 Aug 13 '12 at 7:06
@ErikE - I used "ID" as a shorthand that would be easily understandable by anyone scanning the answer. If you look at my answers on dba.se and SO you'll see that I'm staunchly on the "Don't put the table name in key column" side of that particular holy war. In my databases, I use the name "syskey" for an IDENTITY column used as a surrogate primary key and the pattern [qualifier_]table_key for foreign key names. This avoids bumping into the word ID which is sometimes reserved and it remains predictable without relying on natural joins, which I dislike. – Joel Brown Aug 13 '12 at 12:38
@miracle173 - Thanks for the +1. If I were modelling something for a system I was actually building I might avoid THING as being a bit too vague. I take your point though, how about NEEDABLE_THING? I'd buy weight over rank. Thanks for the comment. – Joel Brown Aug 13 '12 at 12:42
So you name the "same" column different things in different tables, depending on how it's used. I hate that! I think the "key" thing breaks down too fast and caters to the beginner at the expense of the expert. One DB I worked in had "key" and "frnkey" prefixes everywhere--it was a joke of a database and taught me how awful it is to name things by how they are used rather than what they are. – ErikE Aug 13 '12 at 16:22
show 3 more comments
up vote 0 down vote

When you design a database first part is drawing the ER -diagaram after drawing it, you can decide what is the primary key ,foreign key etc .When drawing a ER diagram you can easily find what all things are repeating and can use normalization to that. Certain softwares are also available for designing the ER diagram like mysql workbench etc, i hope this helps

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.