Alternative for SQL Lookup?

Question

I have multiple ProductTables like this:

ProductTable1
ProductTable2
ProductTable3

Please note that the ProductTables are different in design.

I need to reference a customers products, they could have multiple different products - how should I do that?

I could make a Order table (mapping) like this:

[Order]
Id
AccountId
ProductId
ProductType

Then I could insert in ProductType the name of the ProductTable. So in a query I would get an ProductId and the table name (type) of the product so I could make a new query based on that. I guess that is what's called LookUp?

But the downside of this is that the SQL database doesn't know the relations between an Account and the Product.

Is there a better way to achieve this?

I am trying to build a CMDB for our company. Customers can have physical servers, virtual servers, VPN tunnels, Firewalls, other hardware etc. That is why I need to have multiple products because they share little with each other.

`is there a better way to achieve this`: yes; use a proper table design! — Jamiec, Jun 06 '16 at 08:50
I would suggest refactoring your database to contain a single product table that will hold all the columns that are relevant to all products, and then link this table to the different products table with a 1:1 relationship. This way, your orders table can be linked with a 1:many relationship with a single table. — Zohar Peled, Jun 06 '16 at 08:52
You need to store the relationship between a customer and its products. If the relationship is "all products the customer have previously ordered" then the order/orderline tables should give you that relationship. — Lasse V. Karlsen, Jun 06 '16 at 08:52
Why do you have 3 different product tables? Why do you need them? Are they really that different? — Pred, Jun 06 '16 at 08:58
Is there a relationship account >>> orders >>> products? Where orders knows about account and product? — P.Salmon, Jun 06 '16 at 09:45
@P.Salmon that is what I am trying to achieve. But how should the Order table know which ProductTable the product is stored in? — user1281991, Jun 06 '16 at 10:01
SQL is not going to know. You cannot have dynamic row level relationships. — paparazzo, Jun 06 '16 at 10:31

Jamiec · Accepted Answer · 2016-06-06T10:16:01.837

Customers can have physical servers, virtual servers, VPN tunnels, Firewalls, other hardware etc. That is why I need to have multiple products because they share little with each other.

But they should all share (at least) one thing - the product Id, the primary key used to link a product (of any type) with other entities in your database. Moe likely, they also could share a description.

Table: Product
Id: int (PK)
Description (NVARCHAR)

Table: PhysicalServer
ProductId: int (FK to Product)
... other fields relevant only to PhsicalServers

Table: VirtualServer
ProductId: int (FK to Product)
... other fields relevant only to VirtualServer

Using this method, your other tables always link to the PK of Product - in this way you do not have multiple different tables with their own ProductId and you dont necessarily need to know what table the rest of the detail is in for that sort of product - all products share the same productId.

ORM tools like entityframework will also build you the correct inheritance heirachy, allowing for example PhsicalServer to inherit from Product

Yes of course! Was exactly what I needed. Just an extra layer (Product) where all my different products can reference with foreign key. Thank you — user1281991, Jun 07 '16 at 11:32

score 0 · Answer 2 · edited May 23 '17 at 12:31

I'm guessing you've bumped into one of the limitations of relational databases - it's hard to model the inheritance relationship. This is a common theme on Stack Overflow - the best answer is here.

You may want to refactor your database design as a result of reading this - but if you don't, your "find an order" query would need to union on all your product tables.

You may want to introduce the concept of "orderHeader" with orderID and customerID, and "orderLine", linking the orderHeader to the items the customer ordered - your current design means either an order only has one product, OR you are repeating accountID and OrderID for every order line.

score 0 · Answer 3 · answered Jun 06 '16 at 10:35

Your products are not as different from each other as you think. There are stores selling computers, clothes, toys and more, and they still have one single item table, because the items share many attributes.

a product number
a name
a description
a brand name
a price
and maybe many many more

Some may be optional. In above database a color may apply only for 90% of the items. Well, then it's null for the others. So this is one way to deal with specific attributes. Another is to add a key-value table for such attributes. A third is to have additional product group specific tables. E.g.:

product (productno, name, description, price, id_physicalserver, id_virtualserver, ...)
physicalserver (id_physicalserver, ...)
virtualserver (id_virtualserver, ...)
...

Then add a constraint on the product table, so that only always one of the product group IDs is filled.

With

order (orderno, orderdate, clientid, productno )

you'd query the data thus:

select p.productno, p.name, coalesce(ps.size, vs.size) as size
from order o
join product p on p.productno = o.productno
left join physicalserver ps on ps.id_physicalserver = p.id_physicalserver
left join virtualserver vs on vs.id_virtualserver = p.id_virtualserver
where p.clientid = 1234
and (p.id_physicalserver is not null or p.id_virtualserver is not null);

(I'd probably go with only one product table in your case containing several optional fields. This is usually the easiest way.)

Alternative for SQL Lookup?

3 Answers3