2

I'm a SQL noob, and whilst I'm aware of the major tools available, I'm not experienced enough to know the best tool for certain situations.

As an example, I current have a group of tables where referential integrity is needed. Each table does not have all the necessary columns itself to be able to constrain the data, so I have at least 3 options open to me.

  1. Create other table/tables that connect the data together - apart from duplicated data, this leaves multiple files to keep synced.

  2. Create a trigger - not too difficult, but how trustworthy is a trigger? And is it scalable?

  3. Create a function - not something I've done before, but I came across an example showing how it could be used to constrain data stored across multiple tables.

Given what I'm trying to do - maintain integrity by joining data, what should I consider, and are all 3 methods suited to what I'm trying to do?

Here an example using a bridge table to link missing table:

enter image description here

dotnetnoob
  • 10,783
  • 20
  • 57
  • 103
  • 1
    "Each table does not have all the necessary columns itself to be able to constrain the data" why not? How are the tables related? – D Stanley Mar 25 '15 at 16:48
  • @D Stanley - Added an image - please feel free to ask further questions. – dotnetnoob Mar 25 '15 at 16:52
  • 2
    `Attribute` `AttributeValue` `AttributeGroup` - watch out you're not falling into the trap of the [inner platform effect](http://en.wikipedia.org/wiki/Inner-platform_effect) - especially if you're somewhat new to database modelling. – Jamiec Mar 25 '15 at 17:13
  • @Jamiec - "inner platform effect"? – dotnetnoob Mar 25 '15 at 17:16
  • @Jamiec - ah, you mean over engineered! I agree with you entirely - this is part of the problem - on one hand you get told SQL can handle millions of rows, next its don't create too many tables - denormalise, don't over normaise... the list goes on. Given the example above, which tables would you consider combining? – dotnetnoob Mar 25 '15 at 17:24
  • 1
    No, the inner platform effect is not just about over-engineering. Its about trying to recreate a database within a database (in part, at least). Tables like Product, Attribute, AttributeValue are an indication that you're trying to recreate what a database does well, badly (Attribute=Column of Product, AttributeValue = Value within column). – Jamiec Mar 25 '15 at 17:26
  • I see what you're saying - right now ProductAttributes could be made into 2 columns if I was being super simplistic - which could remove the need for another 6 tables. – dotnetnoob Mar 25 '15 at 17:43

1 Answers1

1

Using foreign keys are the best (and fastest and lightest footstep) way to guarantee data consistency. If you want a table of States to guarantee that only valid state spellings (and states that you do business in) are added to sales orders (so when you search for all sales to New Jersey you only have to search for one spelling) to your other tables there is no easier way then to use FKs.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • But when you say "no easier way" - there is a table to create and maintain on an ongoing basis - so that surely brings with it more complexity. – dotnetnoob Mar 25 '15 at 16:57
  • Remember that the state list will contain all the valid states your company deals with. Its a lot easier to Add a state to a table then to a trigger and/or function. You will end up using the state table in your queries. – benjamin moskovits Mar 25 '15 at 16:58
  • I'm not sure what you mean by your state table - it would be good if you could provide an example. – dotnetnoob Mar 25 '15 at 17:01
  • Please see my solution. I suggest that a classic use of Foreign keys is to create a table that contains a list of states. – benjamin moskovits Mar 25 '15 at 17:11
  • I understand now - thanks for that - however what you're describing sounds more like a lookup table - a 1-to-1, wheras I'm describing a scenario to maintain integrity by combining data across multiple tables - I agree it still involves the use of foreign keys, but given the increased complexity, it could result in a different answer. – dotnetnoob Mar 25 '15 at 17:16
  • If you have a table of orders and another table with detail lines for each order there too, almost always, Foreign keys are used to link up the two tables. – benjamin moskovits Mar 25 '15 at 17:19
  • Yes but in the case you've outlined Benjamin, the data is needed in both case - I'm talking about creating tables whose sole purpose is to link other tables. – dotnetnoob Mar 25 '15 at 17:46
  • What you may be describing is a table that allows many to many relationships. For example a table that links students and teachers together (each student may have multiple teachers and each teacher may have multiple students). A foreign key is usally one to many (one state, one order ) to many (many orders from that state, many detail lines for that order). – benjamin moskovits Mar 25 '15 at 17:53