I'm new to the list and have a question regarding an implementation of a
database schema design. I'm looking for any information I can find that
confirms what I think, or tells me I'm completely wrong - and hopefully
where to look for additional answers.
It's a very simple scenario:
An account has a list of products which may have any number of attributes.
My design would be to have a product table that contains a foreign key
to an account table. Another table, say ProductAttributes_xref, relates
a many-to-many relationship between a product and its attributes. The
product's attributes would be stored a separate table - ProductAttributes.
Therefore, give a product key and a desired attribute, you could find
its description by selecting from the ProductAttribute table joining
Product and ProductAttribute through the cross reference table.
However, the current design is quite different and to retrieve the
attribute description I have to do this:
Given a product key, I select a value from a table. The value comes
back as a negative number, which I must convert to positive (obviously
multiplying by -1). I then convert this positive number to a string
where I concatenate it onto the end of a literal to produce the name of
another table that I have to query. I query this table to select a key
that allows me to query another table to get the description.
My first reaction was shock in horror at the design, however I admit
that I don't know everything. Is it possible that the design, given the
reality I face, is based on some advance theory, or an implementation of
the relational model that I'm not familiar with?
Thanks for any and all responses and/or suggestions.
John
