Topic: Schema help or can MVD use composite keys
I am having a problem (i think) with the way MVD allocates the primary key ID. Is there any way from within MVD to have a 'composite ' key?
My problem:
I need to allow for a product to have multiple suppliers.
I would like to display in a tablegrid:
The person who ordered a part: The supplier Name : Order date: Product: Qty ordered. (This is just the basic list of fields).
The current set up fails when I try to include the supplier name (or anything from that table). The error is that that column does not exist. I get the same error if I directly type the SQL into DB Browser for SQLite.
Currently I have an intermediate table that contains the product_Id from the products table and the supplier_Id from the suppliers table, both set as a foreign key.
I think my problem might be that I need to actually have the supplier_id in the intermediate table as the primary key or some form of composite key.
The SQL that MVD produces is:
SELECT DISTINCT "part_suppliers"."supplier_name", "orders"."date", "orders"."order_ref","products"."part_num","technicians"."f_name","orders"."id","orders"."id_technician"
FROM "orders" LEFT OUTER JOIN "technicians" ON "orders"."id_technicians" = "technicians".id
I'm wondering if to accomplish what I'm hoping for, I need to manually create the tables and SQL scripts?
The schema I'm using is here http://myvisualdatabase.com/forum/viewtopic.php?id=6190