Mysql database design

Question

abbyadnez 0 Newbie Poster

13 Years Ago

I'm developing a system to help monitor payments recieved and inventory items.
I was asked to add a payment entity to my erd .. i'm kind of confused which entities it will have relationship with and when i am creating my tables, which foreign keys would be in its colum..
heres my erd.. pls any help would be appreciated.. thanks

mysql

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with pdf files.

Doc1.pdf (50.26 KB)

2 Contributors
1 Reply
80 Views
10 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by tesuji

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tesuji 135 Master Poster · Answer 1 · 2010-08-02T13:57:08+00:00

Nice ERM :)

Invoice against purchase of your customer is related to order. Your relationship –generates< is one-to-many which means that a certain order may have various invoices what is a good idea if your company is dealing with partial shipment. Here invoices usually have their own primary keys which are often the debtor number. OrderID is non-identifying foreign key in invoices. If there is only one invoice per order –generates< is a one-to-one relationship. Then primary key of invoice could be orderID (or is it sales_id ?). Btw, there might also Invoices against purchases of your company itself (from your creditors).

As for Purchase Manager <Purchase Order-< supplier, shall a certain supplier receive only one order by your company? If so, putting purID into supplier is ok. But if your company wants to buy repeatedly from that supplier and former purchases shouldn’t be deleted, your cardinality is wrong. Then you must put supID into Purchase Order. On the other hand a certain purchase order may consist of various products, then you could treat it analogous to customer <order <order line> product, you see?

As for the keys in relational model: You should always define all primary keys and all foreign keys correctly. On mysql you have to choose innodb which fairly supports referential integrity. If there is a chance for using better relational database, e.g. MS SQL server (express), Oracle (express), SQL Anywhere, PostgreSQL, Derby, JavaDB etc., take your chance.

-- tesu