2

Guys can anyone please tell me if the below tables have one to many relationship or not because I am a bit doubtful

Figure

In the table named user I am storing details of user and in the table named useraccesslvl I am storing the useraccesslvl id,useraccesslvl name and useraccesslvl icon.

Shall i leave it like this or is it better to make a junction table so there is a table in between called useraccelvl_user containing the follwoing fields: - useraccesslvl_userid - useraccesslvlid - userid

In my solution I require that all relationships are strictly 1 to many and tables are 3NF

  • To specify exactly what I need to confirm is to check if these two tables conform to the below: One to Many (1-M) relationship: This is also relationship between primary & foreign keys relationships but here primary key relating to multiple records (i.e. Table A have book info and Table B have multiple publishers of one book). (taken from: https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) –  Sep 16 '17 at 16:46

1 Answers1

0

Strictly speaking you do not have a 1-many relationship, nor have you achieved 3NF. It is possible with the design as specified to have two records with the same UserAccessLvlID, provided that the records have different UserAccessLvlName values.

I suspect that this is not your intention. If you intend UserAccessLvlID to be unique, then remove UserAccessLvlName from the primary key on the UserAccessLvl table. Then you will have a 1-many relationship and 3NF in the two tables you've described here.

Brian
  • 1,238
  • 2
  • 11
  • 17