cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chao
Frequent Visitor

Need help dissecting tables

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

@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. _1.jpg

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. 

View solution in original post

6 REPLIES 6
Drrickryp
Super User
Super User

Chao
Frequent Visitor

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
Super User
Super User

@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. _1.jpg

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. 

View solution in original post

Rivius
Resolver III
Resolver III

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.

@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
Frequent Visitor

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,844)