1

I have a table let's say products like this:

  1. Id
  2. Description
  3. Category_Id
  4. Location_Id
  5. Seller

what is the correct way of storing the id of category and location? I was told by a programmer that i have to make a table let's say tblChoices like this:

  1. Id
  2. Code
  3. Value

and to use as value of code 'category' for categories and value of code 'location' for locations. then store the description in the value field.

Is this the correct approach or is it better to use a table for category, and a different table for location? How the 2 ways affect speed of retrieving data? the one way surely has less tables but uses the same table many times with joins to retrieve data

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Nick Massias
  • 25
  • 3
  • Looks like an example OTLT, which most people would probably call an anti-pattern. See: [OTLT and EAV: the two big design mistakes all beginners make](http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html) – nvogel Sep 26 '19 at 17:53
  • It also looks like EAV specifically being used for subtyping/inheritance. This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Sep 26 '19 at 18:10
  • There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Sep 26 '19 at 18:10
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Sep 26 '19 at 18:11

1 Answers1

0

The general approach is to use a different table for categories and for locations.

Why? You can declare foreign key relationships, which in turn help you maintain data integrity.

There are some situations where you might want to store all reference tables in a single table -- for instance, it can be easier to translate the database into another language.

In general, though, you want separate reference tables. After all, you might have additional columns such as categories.long_term or locations.country.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786