-1

My database tables:

1.jobs table having fields id,name

2.locations table having fields id,name

Jobs can have more than one location and locations can have more than one jobs.So i added a table job_locations having id,job_id,location_id.

But my doubt is that if i create a table like this, will it effect normalization ?If i need to connect more tables to locations table then DB will have more tables. (Example : if users table have more than one location and vice versa, i need to create a table as job_users too..)

I have another solution, that is to add location_id in jobs table. So jobs table fields will look like this: id,name,location_id. Then i will store all location ids as array in location_id as string.(But eloquent method will not work here).

which method is better and why?? OR Is there any other solutions?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Vishnu kk
  • 1
  • 2
  • Yes, it will affect normalization BUT in a positive way. Pivot tables are actually used to achieve and maintain normalization, for example, by breaking down a many-to-many relationship. In your mentioned example, a pivot table is appropriate for use. As for your concern about having to add more pivot tables later on, do not be concerned about number of tables but rather focus on achieving data integrity through optimal structure where all your entities exist at atomic level and also relate to each other on that level. – xuhaib Jul 16 '22 at 17:03
  • What is your 1 specific researched non-duplicate question re how you are 1st stuck in what presentation of what design method? What do you mean "affect normalization"? What exactly is the problem? Why would or wouldn't it? You are using a word but your post doesn't reflect anything about its meaning. What does "connect more tables" mean? FKs have nothing to do with normalization. "better" doesn't mean anything in particular. What do you mean & how are you stuck evaluating goodness & better for yourself? [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Jul 16 '22 at 22:27
  • @philipxy..I got some solutions from other users, I think they got my point.Try to read it again and still you are not sure about answering it, i will explain : I am having doubt if I am following correct concepts of normalization.The problem is,I need to get locations for a single job.But these locations can have more than one job.There are 2 options to tackle this. which i had clearly mentioned in my question.one is creating a table(like bridge table).Other is to store multiple location ids in single column as string.Out of this which is good practice and the reason for it? – Vishnu kk Jul 17 '22 at 11:34
  • Please clarify via edits, not comments. Your post is unclear re normalization. Please answer my questions. ["Normalization"](https://stackoverflow.com/a/40640962/3404097) without any other qualifiers does not even mean 1 specific thing. It doesn't mean "good design". The current answer posted is poor & says wrong things re normalization. PS A many-to-many "bridge"/"junction"/"join"/etc is the straightforward representation of a M:M relationship. Your design choice is a duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/3404097). – philipxy Jul 17 '22 at 18:40
  • Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Jul 17 '22 at 18:41

1 Answers1

-1

No it will not affect normalization.

such bridge tables(weak entities) are used to produce 2NF

See for example https://geekyisawesome.blogspot.com/2011/03/database-normalization-1-2-3-nf.html

A Bridge table must be used for such m:n relationship to conform with normalization, as else you would have multiple identical values in a table.

nbk
  • 45,398
  • 8
  • 30
  • 47