Need help dissecting tables

[Chao]

Hi,

So I am creating a simple project app that requires me to store and retrieve customer data but I have trouble splitting the data tables into smaller ones. I am using Excel as the datasource and I will try to explain the details that I need using a fast food example.

A customer can have many orders but an order can only have one customer. Customers have the selection to choose either one, or a combination of one of the following in their orders:

- Burgers

- Fries

- Drinks

If a customer chooses Burgers, I will need to record the serial number, cooking temp, brand and extra remarks(if any) of their ingredients. For example if there are 4 ingredients in a burger, the data for each ingredient will have to be stored. The same goes for Fries and Drinks, but with only 1(fixed) ingredient in each. 

Also, is it possible to have a toggle in the [orders] tab to indicate what the customer have purchased? For example, Customer A purchased Burgers + Fries, so in a new order the toggle for Burgers and Fries will be switched on, and I will be able to insert new data only into those 2 sections, with the Drinks option blanked out because the toggle is switched off. 

I have all the unsorted data in paper form, but I have trouble figuring out how to separate it into different tables in Excel. I have tried splitting the data myself but the Primary and Foreign keys are giving me headaches. Any help from anyone will be much appreciated. Thanks in advance!

[Drrickryp]

@Chao 

Please check out my blog series for how to set up your tables.https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Database-Design-Fundamentals-and-Power...

[Chao]

I read your blog hence I understood the concept, but I have trouble implementing them which is why I posted my query here. The issue I'm facing now is I don't know how many tables I should separate my data into because each table can only have 1 unique column, and in my project I have 4 objects in a single order, where each objects has a unique serial number(column). 

If you don't mind, could me tell me roughly how many tables will there be according to my scenario? Also, is it possible to create the toggle like I mentioned in my post?

[Drrickryp · Accepted solution]

@Chao 

Excel is a poor choice for the backend of a database that is more complicated than to enter data into a single table.   I suggest you consider SharePoint instead (or Dataverse or SQL if your lists will become very large). Any of these data storing programs will automatically assign a primary key (ID) to your lists.  That being said, conceptually, tables (or lists in SharePoint) should group records with a common theme.  In your case you are dealing with a minimum of three common items, Customers, Orders and Products. 

You app contains a many to many relationship between customers and products.  The Orders table contains the foreign keys from the other two tables.  You could put additional information regarding the product in the Product table for example, stock on hand, a description of the item.  With a design like this, you could track Orders by customer as well as looking at the product and seeing who has ordered it and in what quantity. 

[Rivius]

If you really want a solid database structure, do not use Excel for this.  Relational database via Dataverse or SQL is the way to go like @Drrickryp suggested.

[Drrickryp]

@Chao 

When you have some time, install the Northwind Traders database and go through the exercise of building the app.  You will learn much from it.  I know I did. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/northwind-install 

[Chao]

Thank you both of you! I will now try to switch to SQL, which is another thing I have to learn from scratch.