Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2003-04-08 : 18:32:17
|
| Hi I have an application which has a dropdown list containing values from a table. Should this field have FK relationship with the table? The problem is this field may or may not be visible. If the field is not visible the value stored in the db is "". To create a FK relationship with this table, there needs to be a value of "" in the database. I can set the visibility of the items in the dropdown so this "" field can be hidden, but I'm just curious how far should I go with adding FK contraints.Nic |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-04-08 : 23:58:31
|
| If this is a combo that supplies values for a column, it's not really a foreign key candidate, especially since you're saying it can be empty, and you shouldn't really have that in a PK/FK relationship. More likely you need a column constraint to limit the values to the ones you allow, although this seems to be easier done on the front end by choosing a Dropdown List style that does not allow the user to type an entry not in the list.Another note: Why are you storing "" (empty strings)? Why not nulls? For comparison sake, a null is not the same as an empty string, so if you would try to create a relationship between these tables, they wouldn't necessarily match.Sarah Berger MCSD |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-09 : 05:18:58
|
| Your application is a presentation layer to display information to the user.The database is a data store which contains data integrity constraints.You do not display constraints (FKs) to the user and the application should have no knowledge of the database structure.The tables may have FKs on them but that depends on how the data is stored.Get away from the concept of the application getting data from a table - it should receive data from an SP which will retrieve the data from the database. This may be from a table but it may also change at any time as the database structure is amended without affecting the application.In general values displayed to the user are presentation items and are not candidates for integrity constraints as they may need to be amended without affecting the connected data structures.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2003-04-09 : 13:54:37
|
| Thanks for your input but I'm confused. If there shouldn't be a PK/FK relationship between a "Lookup" table and the table that stores the value, then what happens when someone deletes a value from the "Lookup" table?ie. (Lookup table)table: "UserStatuses"INSERT INTO UserStatuses (UserStatus,Description,Visible) Values ('','',0)INSERT INTO UserStatuses (UserStatus,Description,Visible) Values ('g','Good',1)INSERT INTO UserStatuses (UserStatus,Description,Visible) Values ('b','Bad',1)INSERT INTO UserStatuses (UserStatus,Description,Visible) Values ('e','Excellent',1)Then table "user" has a column named UserStatus. There is a PK/FK relationship between the two tables. This way we are ensured the only valid statuses are stored in UserStatuses. To populate the combobox (actually stored in a sproc):Select UserStatus, Description from UserStatuses Where Visible = 1(In the situation where the entire UserStatus field is not visible to the client front-end, then the '' value will be stored in the db)I asked a similiar question before and was told that having many lookup tables (with PK/FK contraints) is the only way to ensure data integrity.http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=23109(5th response on the post)Sorry I know there is probably more than one answer but I'm kinda winging it and this forum has provided TONS of help.Nic |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-09 : 15:41:40
|
| First, just because a certain value should be hidden from users does not mean that the value itself must be invisible! You can put whatever value you want as the hidden value (such as "<Default>" or "<n/a>" or whatever you like) and when the client displays that value, just hide it.You could even have your look-up table like this:ValueID, Description, Hidden? (bit: 0=no, 1=yes)And in the client, if Hidden? is 1, you hide it that option, otherwise you show it. You could even have another field, called display value, and in that could reside the final "" and other descriptions that show on the screen.Remember, just because it's a "look-up" table doesn't mean it can only have 1 field! I ALWAYS include fields like these, alternate values, sort values, boolean fields, and all that, in my lookup tables.An example: Suppose you have a table of "Invoice Statuses" with values of:Status-----------PreliminaryOpenOverdueClosedAnd you write a report to show you all invoicces that you are awaiting payment on. So you have a WHERE clause:WHERE Status in ('Open','Overdue')However -- in my opinion, the BETTER way to do it is to add a field to your "Invoice Statuses" lookup table:Status, AwaitingPayment-------,----------------Preliminary,0Open,1OverDue,1Close,0And then your WHERE clause becomes:WHERE AwaitingPayment = 1Get it? It makes maintenance of your system easier, your code easier to read, and now you are storing valuable information in your lookup table for your applications to use. I NEVER include code like:WHERE StatusCode in (1,40,12,3,3) Because I will always ask myself: "Why are you filtering for those codes?" and if the answer is "because that means XYZ must be true", then I add a boolean field called XYZ to my look-up table and filter using XYZ = 1.(sorry, got a little side-tracked. I think "look-up tables" is going to be my next article; no one really knows how to use them. The thread you linked to with the solutions given is a perfect example of useless look-up tables w/o any attributes.)Having said all that, I would also definitely create a FK relationship for all of the reasons you mentioned. That is the best way to ensure integrity, and there IS a logical relationship between the two tables so why not make it physical in this case as well.- JeffEdited by - jsmith8858 on 04/09/2003 15:48:03 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Add Constraints?