[DimensionX]

I'm designing a database at the moment and well with access if you make a drop down box you usually put the contents of it in a separate table, does this look ok?

Client table
=========

firstName
surname
address
town
state (dropdown contents is client side)
zip
occupation
telNumber
dateStamp
referredBy (drop down, contents taken from referral table)
clientId (primary key)

Referral table
==========
referralId (primary key)
clientName (taken from client table using clientId to look up (though not sure how to do that))
clientId (foreign key)

does that look stable? I wasn't sure what to do with the relationship, I added it in because i thought it was bad form to have a form that creates a new client by looking up itself....but i'm not sure is that actually a problem or not? :S


I put the contents of the dropdown box for the state client side to ease server traffic instead of creating a table in the database since there will be quite a number of users using this and it's doubtful that there will be anymore states.

any help will definitely be appreciated

[CreativeClans]

I don't know what database you are going to use, but a table does not contain a drop down. Populating and showing the dropdown when the user is inserting new data, has nothing to do with the database design.

If a reference is another client, then referredBy will contain a clientID, and thus becomes a foreign key to the client table itself.

A second table (Referral) is needed if a client can have more than one referral. That table will contain at least two fields:
clientID
referredBy

Together they're the primary key, and both are foreign keys to the Client table.
In this case, there will be no referredBy field in the Client table.