Moving from FoxPro flat files to relational database. I am working out the design in Access but will eventually move the live system will be in SQL Server. I can email image of design or the Access database.
The problem is two paths that both start in one table [P] and end in another [S]. It doesn't look right. Does anyone know how to solve this design problem?
[P] -- [PM] -- [M] -- [G] -- [S]
[P] -- [PT] -- [T] -- [S]
[I] -- [S]
Because [P] had many:many relationship with [M], intermediary table [PM] created with primary keys of [P] and [M] combined to form composite primary key in [PM].
Q1: [PM] should have 1:many with [M] but the relationship created was 1:1 and I can't edit it.
[M] has 1:many with [G]. [G] has 1:many with [S]
Because [P] has a many:many relationship with [T], intermediary table [PT] created with primary keys of [P] and [T] combined to form composite primary key in [PT].
Q2: Should [T] be linked to [S]? What I want is the ability for the user to select T info from T and place into a S record, but I want to limit the choice of T info, based on the choice of P record. (With or without a link on Relationships tab between T and S, the T field in the S record allows a query into T. So is the link necessary? But I still don't know how to code to limit the T info to those relevant for the related P.)
Q3. [I] has a 1:many relationship with [S]. [I] will be used to combined multiple records from [S] into a single output. Is it OK to have multiple 1:many relationships with 1 from other tables and many in a single table
>> Stay informed about: database normalization help?