Database Design - Need Advice!!! 2

[khwaja]

Need some advice on my database design which is probably not right as I am having trouble in being able to add records which could update other tables.

I have following three tables:

tblStores (fields include StoresID, Numerical - (PK), Store Name, Address, phone, State etc)

tblLease (Fields include LeaseID, AutoNumber - (PK), StoresID (FK), and a number of fields relevant to lease info. This has one to many relationship to tblStores as one store may have a number of leases, store lease, liquor store lease etc.

tblStats (Fields include StatsID (Auto number - PK), StoresID (FK), and a number of fields covering yearly sale information of stores. One store may have sales in many years 1996 through to 2002.

So in short, tblStores has 1-To-Many relation to both other tables.

On paper this designed seemed OK until I realised the need to add a new store. With existing design, a new store can be added in either of the 'many' tables but will not be reflected in the other many table. I must admit that this is highly inefficient design. I looked at the option of creating a junction table but just could not figure out how to use that in this situation.

Would like to have some guidance on this.

Regards

 

[DanJR]

hi there,

I think there is nothing wrong with your db design. Although, perhaps you should add some referential integrity to stop users from adding stores in the 'many' tables without the store being present in the stores table.

Users should add stores directly into the stores table. In practical terms an example of allowing users to add stores while they are adding lease data *could* be to write code in the Not_In_List event of a StoreID combo box which displays a message "This Store is not in the database - would you like to add it now?". If the user says yes, then either display the 'Store' form or use code to add the new store information to tblStores.


Does this help?
Cheers,
Dan

 

[khwaja]

Thanks Dan. Referential integrity has been aded with reference to each relationship but this needs to exist to check both secondary tables and one.

Re yoir second point to pop up a stores form, I have provided a seperate form for the user to add a store to enforce discipline instead of creating one when in the Lease form using a command button. But this seperate form is currently based on tblStores and tblLease tables with storesID as the link. This is where I thought there must be some way of reflectiing the new store to the third table -tblStats but could not figure out how to do this? So effectively, unless design needs to be amended in some way, I need to be able to reflect stores addition (or update FKs) in other tables simultaneously. Not_in_List option is good but it takes me to the form, then I have same issue of being able to add store in the third table. Any more insights?

Cheers

 

[DanJR]

Hi khwaja,

sorry, i'm getting a little lost now...

When you say that you have a problem adding the newly created store in the third table - are you refering to the new store not being present in the combobox list of storeID's? If so, then just requery the combo box:

me.StoreID.requery

Am i on the ringht track here?

Cheers,
Dan

 

[khwaja]

Sorry for confusing you mate. I think you have raised a valid point which can be useful in ensuring that, once added, new record is invaiably shown in the third table. After some experimentation, I can now see that if I create a new record using a form based on lease info, that record is visible in the third table, so there are no issues.

Thaks a lot for your time and effort.

Regards

 

[kaushal78]

Hi khwaja,
i have read the query which u send u question is when i add a new record in the Store_master table the recorde should be added to the other three table am i going right...
Solution:----
when u give relationship in the Datatype you have lookup wizard use the property it will ask which column do u want take the column from the store master table and ur query would be solve or else
write the module call the store id from the store master
and update to all the table..
Bye
Hope ur doubts would be solve

 

[khwaja]

Thanks a lot Kaushal. This is probably a better way of ensuring that records are updated in all other tables. Good on you.

Regards