database design - how to do the relation in this case - items, articles, and locations? -

i have table information of items, articles, can have in many locations. example, can have item in warehouse or in shop. have table shops information , other table information of warehouse.

how best way relation itms location in can be?

i think option have in table items 2 foreign keys, 1 shop , other warehouse. if have fk in shop, fk of warehouse must null. if in future item can in other locations, can add new field in items table.

but when want show information, must check fk not null , show information of concrete location. if use view show information of items , main information of location, have many fields null because not location in item.

but best way? there other options or correct way it?

thanks.

i think option have in table items 2 foreign keys, 1 shop , other warehouse.

this legitimate solution should work fine when there few kinds of locations can link to. if there (or may in future) many kinds of locations, consider doing this:

the symbol denotes inheritance (aka. category, subtyping, subclassing, generalization hierarchy etc.). please search "subtype relationships" in erwin methods guide more info, , take @ this post details on possible implementation strategies in physical database.

see this post , this post.

but if use view show information of items , main information of location, have many fields null because not location in item.

nothing that. if 2 pieces of data have different structure can'y blindly "force" them uniform rows. either leave fields optional (as do), or common fields (if can live that), or use separate query/view each kind of location - there no rule says must in single database round-trip¹!

---

¹ actually, depending on client library, might able pack multiple logically independent queries in single database round-trip.