Database design concepts: is it necessary to have a primary key when I use two or more foreign key? -

i not sure if doing things right here:

1) ok put in 1 table user information (name, last name, mail, password, city , state)?

2) ok how cities related states?

3) user able create challenge 1 subject, level, in 1 state, 1 city , 1 date. (single_challenge table). 1 or more users can register challenge (registration_sc). necessary have primary key on "single_challenge" table, when use 2 or more foreign key? design well-posed?

4) if want allow user create tournament challege, (same single_challenge table) 2 more options: "maximum inscriptions" , "end date". should create table called "tournament_challenge" or can add these 2 fields "single_challenge" table?

i think design decicions half engineer-matter , half artist-matter. there things can tagged bad practice, many other depends on many factors , linked "application domain" reality. me, question falls category:

1) there 1 bad thing: should keep city id, not state id, because state id derived city id (it can retrieved city id) rest seems fine if have decided not possible user have several addresses.

2) think ok. 1 city belongs state, state holds several cities (one-to-many relation)

3) here again, should remove state id, because city id enough. id_user fine, remove , qualify relation (registrations_sc) attribute pointed order of registration. first registered user challenge creator.

about primary key: whether have several foreign keys or not not relevant. need yo know if combination of fields determine uniquely whole row. candidate key. if user can place challenge in city @ given level 1 subject in single date, {user_id, city_id, level_id, subject_id, date} candidate key , don't need primary key. anyway, (and should) still decide have own autonumeric, single-field primary key performance , simplicity reasons. in case, should not forget define unique-index on combination of fields of candidate key, cannot insert duplicates.

4)again, take 1 path or other. there several ways implement it. can add fields table , leave them null single challenges. can use different tables. can use single_challenge table both, , create table keep "maximum inscriptions" , "end date", along foreign key of "single_challenge". table populated challenges tournaments. must know how going exploit model, , design implementation that: 1) technically correct (but many can be) 2) , practical , make life easy (simplifying querys, insertions, deletions, etc.)

i hope can help.