1

I have a table with the information of the items, articles, that I can have in many locations. For example, I can have the item in the warehouse or in the shop. So I have a table with the shops information and other table with the information of the warehouse.

How is the best way to relation the itms with the location in which can be?

I think that an option is to have in the table items two foreign keys, one for the shop and other for the warehouse. If I have a FK in shop, the FK of the warehouse must be null. If in the future the item can be in other locations, I can add new field in the items table.

But when I want to show the information, I must check which FK is not null and show the information of the concrete location. But if I use a view to show the information of the items and the main information of the location, I have many fields null because is not the location in which is the item.

But is this the best way? There is other options or is this is the most correct way to do it?

Thanks.

Rui Jarimba
  • 11,166
  • 11
  • 56
  • 86
Álvaro García
  • 18,114
  • 30
  • 102
  • 193

2 Answers2

1

I think that an option is to have in the table items two foreign keys, one for the shop and other for the warehouse.

This is a legitimate solution that should work fine when there are only a few kinds of locations you can link to. But if there are (or may be in the future) many kinds of locations, consider doing this:

enter image description here

The symbol enter image description here denotes inheritance (aka. category, subtyping, subclassing, generalization hierarchy etc.). Please search for "Subtype Relationships" in ERwin Methods Guide for more info, and take a look at this post for some details on possible implementation strategies in the physical database.

See also this post and this post.

But if I use a view to show the information of the items and the main information of the location, I have many fields null because is not the location in which is the item.

Nothing will help you with that. If two pieces of data have different structure you can'y just blindly "force" them into uniform rows. Either leave some fields optional (as you already do), or get only the common fields (if you can live with that), or simply use a separate query/VIEW for each kind of location - there is no rule that says you must get everything in a single database round-trip1!

1 Actually, depending on your client library, you might actually be able to pack multiple logically independent queries in a single database round-trip.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

It sounds like you should have a location table, and then have the item include a reference that table via foreign key.

Item
-----------
ItemId (PK)
ItemName
LocationId (FK)

Location
-----------
LocationId (PK)
LocationName
Phil Sandler
  • 27,544
  • 21
  • 86
  • 147