I am new to SQL and designing a new database/webapp using A5V10.5 as the front end. I have designed desktop databases using A5v10.5 with DBF files before but, as I said, new to SQL and new to webapps. I came to the Alpha Community with my questions because I know many of you use SQL tables as a back end database for A5. I am using MariaDB which is much like MySQL, and Navicat to design using a GUI. At any rate, here is what I am doing and what I need to know (for right now).
Perhaps the best way to represent he table relationships would be as follows:
salesorders (the main table, has 13 columns; do not need to list all of them)
salesorders.sonumber (pk)
salesorders.ponumber
salesorders.custname
The other eight tables are(table name in bold):
1/ Customer customer.custid (pk)
customer.sonumber(fk) > child link to salesorders.sonumber(pk)
2/ custaddr custaddr.custid(pk) > child link to customer.custid(pk)
The above (italics) is one of my questions. The child link of custaddr.custid(pk) to customer.custid(pk) is two primary keys linked where custaddr.custid is both the pk and fk for that table. Can I do that? Can I link two primary keys?
3/ ponumber ponumber.sonumber(pk) > child link to salesorders.sonumber(pk)
ponumber.ponumber
4/ hwsup hwup.sonumber(pk) > child link to salesorders.sonumber(pk)
5/ swsup swsup.sonumber(pk) > child link to salesorders.sonumber(pk)
6/ instlsup instlsup.sonumber(pk) > child link to salesorders.sonumber(pk)
7/ hwprodshwprods.ponumber(pk) > child link to ponumber.ponumber
8/ swprods swprods.ponumber(pk) > child link to ponumber.ponumber
As can be seen above, I have several primary keys in child tables (functioning as foreign keys) linking to primary keys in parent tables. I need to know if I can do this.
In addition, the ponumber table has only two columns, sonumber(pk) and ponumber. The sonumber column really needs to be a fk referenced back to the salesorders.sonumber (pk) to establish a one-to-many parent/child relationship with salesorders table being the parent and ponumber being the child. As such, the ponumber table will have more than one occurrence of the sonumber (not unique) so the sonumber cannot really be a primary key in the ponumber table. The question here is, can the sonumber column be a foreign key and not be unique? Or, in other words, does a foreign key in a table have to be unique?
gitpicker
Perhaps the best way to represent he table relationships would be as follows:
salesorders (the main table, has 13 columns; do not need to list all of them)
salesorders.sonumber (pk)
salesorders.ponumber
salesorders.custname
The other eight tables are(table name in bold):
1/ Customer customer.custid (pk)
customer.sonumber(fk) > child link to salesorders.sonumber(pk)
2/ custaddr custaddr.custid(pk) > child link to customer.custid(pk)
The above (italics) is one of my questions. The child link of custaddr.custid(pk) to customer.custid(pk) is two primary keys linked where custaddr.custid is both the pk and fk for that table. Can I do that? Can I link two primary keys?
3/ ponumber ponumber.sonumber(pk) > child link to salesorders.sonumber(pk)
ponumber.ponumber
4/ hwsup hwup.sonumber(pk) > child link to salesorders.sonumber(pk)
5/ swsup swsup.sonumber(pk) > child link to salesorders.sonumber(pk)
6/ instlsup instlsup.sonumber(pk) > child link to salesorders.sonumber(pk)
7/ hwprodshwprods.ponumber(pk) > child link to ponumber.ponumber
8/ swprods swprods.ponumber(pk) > child link to ponumber.ponumber
As can be seen above, I have several primary keys in child tables (functioning as foreign keys) linking to primary keys in parent tables. I need to know if I can do this.
In addition, the ponumber table has only two columns, sonumber(pk) and ponumber. The sonumber column really needs to be a fk referenced back to the salesorders.sonumber (pk) to establish a one-to-many parent/child relationship with salesorders table being the parent and ponumber being the child. As such, the ponumber table will have more than one occurrence of the sonumber (not unique) so the sonumber cannot really be a primary key in the ponumber table. The question here is, can the sonumber column be a foreign key and not be unique? Or, in other words, does a foreign key in a table have to be unique?
gitpicker
Comment