ERD for a php function that generates a invoice

[webdeveloper123]

Hi,

I've created a database design and I would like some input from other members on it. I don't think I've done it correctly as pType is stored twice, once in Products & again in productType. I'm also not sure if I have the multiplicity correct. This ERD is intentionally simple. I did not want to have different model numbers, colours etc. Having said that, any feedback is welcome. I just wanted to keep it simple as the ultimate goal was to generate an invoice , within a function , in PHP. The invoice is similar to the invoices generated by ebay. Any feedback is welcome.

Thanks

 

 

Edited May 14, 2022 by webdeveloper123

[Barand]

Product_type   -1----------< Product

ie One to Many

 

+--------------+                +--------------+
| product      |                | product_type |     
+--------------+                +--------------+
| product_id   |       +--------| pType        |
| pName        |       |        | typedescrip  |
| pType        |>------+        +--------------+
| pDescription | 
+--------------+

While I'm here, your members table needs a memberID as its primary key otherwise you have nothing to join on.

Your model allows only single product per order - it that what you want?

Edited May 14, 2022 by Barand

[maxxd]

As Barand pointed out, each order only allows one product as of now; you'll need an order details table that tracks order number, product number, and product quantity. I'd also recommend tracking product price in the order details table as well, as this can fluctuate over time and you'll want an accurate historical record.

I'd also recommend putting things like shipping address, as well as contact email and phone in a separate member order details table because people move, or order things to be shipped to different locations, etc. but I'm not sure if that's a concern for your current needs so take it with a grain of salt.

[webdeveloper123]

Thanks for the tips Barand. I had an inkling about the member table needing a memberID but I wasn't sure.

16 hours ago, Barand said:

Your model allows only single product per order - it that what you want?

No. Again when I was creating the model I had a suspicion that is what I inadvertently did.

@maxxd Yes on the contact email and being shipped to separate locations that is out of scope for this mini project. But thanks for your input. Would order details be a new entity or replace order?

 

[Barand]

                                                     +--------------+       +--------------+
+----------------+                                   | product      |       | product_type |  
| order          |                                   +--------------+       +--------------+
+----------------+                               +---| product_id   |   +---| pType        |
| orderID        |--+                            |   | pName        |   |   | typedescrip  |
| memberID       |  |    +------------------+    |   | pType        |>--+   +--------------+
| orderDate      |  |    | order_item       |    |   | pDescription | 
+----------------+  |    +------------------+    |   +--------------+
                    |    | order_item_id    |    |
                    +---<| orderID          |    |
                         | productID        |>---+
                         | qty              |
                         +------------------+

 

[webdeveloper123]

Hey Barand, thanks. Do you think I could use the following as a final data model?

 

[Barand]

The relationship between order and and product is via orderItem

[webdeveloper123]

Ah ok. So like this? Could this be the final model?

 

[maxxd]

Not really sure what the purpose of productType is in this schema, but you're still attaching the price only to the product. If you fulfill an order in January, then raise the price on that product in March, when you run a historical report in August your accounting will be off. Track the price of the product at the time of the order in the orderItem table, then use that for all reporting.

[Barand]

Consider it a good staring point for the final model. Models tend to evolve as development of the project progresses.

[webdeveloper123]

Thanks Barand. @maxxd Do you mean like this? Shall I go for this model?

 

[Barand]

I'd recommend using a DECIMAL type for currency rather than FLOAT e.g.

price DECIMAL(10,2)

so you have a fixed number of decimal places.

[webdeveloper123]

6 minutes ago, Barand said:

I'd recommend using a DECIMAL

Done that

[maxxd]

That's how I would set it up, yeah.

The only other thing is I've seen using INT for price and storing all prices in the smallest unit of currency. I'm American (please don't judge me) and we use dollars and cents, so instead of 14.25 for a product save the data as 1425 then divide by 100 (100 cents per dollar). PHP and JavaScript can have some problems with floating-point calculations and rounding, especially when dividing or multiplying.

[webdeveloper123]

ahh ok. I think decimal is fine, but thanks for your advice.

[gizmola]

Here's a few suggestions for you:

• Convention is important.  Pick one and stick with it.  You change your convention in nearly every table.
• Make your table name singular.
  • Member  (not Members).  Product (not Products).  Notice you made a table named Order.  This is an example of the many inconsistencies you introduced.
• If a table or column is a compound word, use underscores for spaces.  Don't use camelCase.  Save that for your PHP code. 
  • productType:  change table name to product_type.  Note you did this for some columns like order_item_id (although as a pk, that should just be named 'id'.
• Make all table and column names lower case.  Don't mix case.
  • Example:  orderID
• Use underscores for words in tables and columns
  • Example:  orderDate.  (Use order_date)
• Don't add in the table name for columns in the table
  • Example:  productType. typedescription.  (Just name column "description".)
• Name any sequential primary key columns 'id'.  Don't add in the table name.  (For Members table you inexplicably named the column userID.)  Just call this column 'id'.
  • Example:  productType.pType??  Should just be id.
• When relating tables together, if a relation creates a foreign key column in the table THEN name that column  tablename_id.
  • Examples:  In product table, product_type_id will be the fk to product_type.id.

If you consistently apply a list of rules/conventions like this:

• it is much easier to use ORM's to map from the relational tables to model classes.   Many ORM take conventions into account by default and mapping works automagically.  Otherwise, you often have to make map file tweaks.
• Writing your embedded SQL code is simpler and easier to understand, because you know what a particular column name should be called. You know what columns are foreign keys, and which columns you need for making your joins.
• There is far less change of a typo screwing up a query you write.  SQL statements are less likely to have errors.  You can also clearly differentiate sql tablenames and columns from variables you create to represent values you fetched from the database.  You also already will easily know the array key names when using routines that fetch a row into a php array, indexed by column name.

[webdeveloper123]

Hey @gizmola, thanks for the input. I took on board most of your advice and implemented it.

Now I have decided not to have a member registration system, thus changing the data model. Instead I have a login form (user,pass is hard coded) & that will act as a fictional member of staff logging into the fictional company system and generating invoices for the clients. I have an updated version of the data model and was seeking advice if it is good to go.

Thanks

[gizmola]

My advice on the specific things to change:

1. All the sequential keys should be renamed to id.
   1. example:  product_id should be id.
   2. When it is a foreign key in a table, then you use tablename_id.
      1. So for example, order_item.product_id is correct, and that links the "non-defining"  One-to-many relationship between Product and order_item.
2. Fix product_type columns
   1. PK should be id
   2. type_descrip should be description.
   3. Since product_type is a foreign key in product
      1. p_type will be named product_type_id
3. Other product fixes
   1. p_description should be description

 

In regards to your shortcuts, I would not do what you are doing.  I would suggest that you just have a member table, and you load that table with 1 row.  Your code can then bypass what you need to, but still be accurate and relationally correct.

From what you have, and what I would have you do:

1. Rename member_detail to member. 
2. Load your one user into member, with id = 1.  Then you can use this special user for orders.  A good way to do this would be to define a constant = to that key of 1.
3. Recreate the relationship between member and order.  The link should go from member.id to order.member_id in the modelling tool.  Again it is a non defining one to many relationship. 

 

 

[webdeveloper123]

thanks

[webdeveloper123]

On 5/15/2022 at 3:22 PM, maxxd said:

Not really sure what the purpose of productType is in this schema,

actually that's a good point. If I have a new product, I can just add it to the product table. Now that I am populating the db, I have realised that product_type.type_descrip serves the same purpose as product.name and I don't think I need product.p_description because it's not like a e-shop where you read the product description and decide on a purchase, it's just an internal system to generate an invoice.

[webdeveloper123]

Would it be fine to just remove product_type?

[maxxd]

Unless there's a specific business reason to keep it, yeah. Programmatically it won't make a difference assuming you update any queries that currently try to select it.

[Barand]

9 minutes ago, maxxd said:

you update any queries that currently try to select it.

... or reference it anywhere else in the query (eg JOIN ON, WHERE, ORDER BY)

[gizmola]

The purpose of a table like product_type would be to have categories of products.  If you don't need that, then there's no problem removing the table.   If for some reason you need to have a grouping of products by category, then you should keep it.  

The product_type.description is not redundant with the product description.  It contain the name of the category, like 'software', 'hardware', 'drugs', 'groceries' or whatever else.

[webdeveloper123]

10 hours ago, gizmola said:

The purpose of a table like product_type would be to have categories of products.  If you don't need that, then there's no problem removing the table

I agree.

Thanks guys