[resolved] 1)Null value in Foreignkey

[sara_23apr]

I have two tables 1) tblCustomer (ID, Name, City) 2) tblemp (ID, NAME, Dept.ID, tblcustomer.ID)

Both the tables have ID as PK
A emp can be either assigned a) All customers b)single customer c) NO customer

Pls note:- there will never be 2 or 3 customer linked to emp (my actuall requirement tables are different but to explain i am using the above tables)

I know how to assign single customer......but had problem how to link all customers and "no customer"

Please tell me if the following solution is right?
1) I will manually insert a record in tblCustomer with id 0 as " all customers" and will not allow the user to delete it
2) store null in FK if it is "no customer"


Also please tell me is it ok to store one more value in tblCustomer as -1 and take it as "No customer"

I have a DDL in the Employee page which should be displaying the names of allthe customers with 2 extra values "ALL CUSTOMERS" and "SELECT" Select is the default value in DDL which says "NO CUSTOMER" selected yet

Thank you
Sara

[techgnome]

No.... don't Store the All or None options in the database .... What we do here in these kinds of situation is to let the Select statement return them like this:

SELECT -1 as CustID, "No Customer" AS CustomerName
UNION ALL
SELECT 0 As CustID, "All Customers" AS CustomerName
UNION ALL
SELECT CustID, CustomerName
FROM tblCust

This way when the recordset comes out in the app and we send it to a dropdown, "None" and "All" are part of the list. Then we store -1 or 0, or the CustID in the table when saving the record.

-tg

[sara_23apr]

Hi techgnome

Thank you for a quick reply.
I think this will solve my challege. This should also be helpful for me if at all i need more values(i doubt if i need, but still .... ) ...i can use -2,-3 etc

Thank you very much

Sara

[sara_23apr]

I tried the solution, but since the "tblcustomer.ID" in tblemployee has a FK relationship i am unable to insert 0 and -1 to the "tblcustomer.ID"

Now should i remove the relation ship? will it become a weak design if i remove the FK relationship?

Thank you
Sara

[techgnome]

That's the down side of this solution. If you are explicitly relying on the FKey to perform deletes, then it may need to be re-thought..... If how ever, it's for linking purposes and selection only, it's perfectly OK to remove the FKey. What you may want to do at that point is create a non-clustered index on that field for faster lookups.


-tg

[sara_23apr]

Dear techgnome
Thank you for the reply and solutions

I have decided not to make the column relationship FK when i need to include new values in the child table column which r not present in the Parent table(PK).

As of now i do not rely on the FK relationship for delete

regards
Sara