November 25, 2021 at 9:21 am
Please advice if the tables have the correct or appropriate columns to create relationships among them?
https://drive.google.com/file/d/1iyiIbVkYCWI0pGNcXD3Fs_A8g0xwj276/view?usp=sharing
November 25, 2021 at 7:10 pm
I have not downloaded the files (as I don't generally download files from random people on the internet), but I don't think you are going to get much response with what you posted.
Now as I said, I am just guessing. But if you are looking for appropriate columns to create relationships between them, it depends on what sort of relationships you are looking for. I imagine that a product goes on an order and an opportunity goes on an order. So Products and Opportunities should each have their own identifier (ProductID and OpportunityID for example) and then Orders would have a column that referenced the identifiers (ProductID and OpportunityID for example).
Makes it nice and easy to create relationships between tables when you have a single column to look at to join the 2 tables. There are other ways to do it, but they add a lot of overhead (disk space and data maintenance). Chances are you are doing a 1 to 1 relationship between Orders and Opportunities in that a single order can only be tied to a single opportunity, but you may do a many to 1 relationship if you have multiple orders per opportunity. Then you are likely doing a many to many relationship between products and orders as a single product can be on multiple orders and a single order can have multiple products.
In the end though, I don't think 3 tables is how I would design this system as you will end up with a lot of duplicate data in a column. For example, I would have a customers table which keeps track of all customer information and a single customer would be tied to multiple opportunities. I would also have a warehouse table and a single product could be tied to multiple warehouses. Might want a shelf location table too as a warehouse could have multiple shelf locations and a product could be on multiple shelves. This allows you to do inventory management apart from just the quantity.
But to address the question about relationships - as long as you have it documented how tables and columns relate, you can do it however you like. For example you could have a productID column in products and have PID column in orders and these two could map up. Not sure why you would do it that way, but you might.
I've seen systems that use PPN AND ITM interchangeably inside the system on the database side.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 25, 2021 at 9:56 pm
Zip file is literally the same concern I had initially - I don't download files from unknown sources.
Posting the DDL to the forum will get you better responses to your questions too. A lot of people on the forum are like me and won't download file randomly thrown on a forum. BUT if you post DDL to the forum, we are likely to help!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 26, 2021 at 1:21 pm
USE [Edu]
GO
/****** Object: Table [dbo].[Products] Script Date: 2021/11/26 15:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[id] [varchar](50) NULL,
[created_at] [varchar](50) NULL,
[description] [varchar](50) NULL,
[name] [varchar](50) NULL,
[updated_at] [varchar](50) NULL
) ON [PRIMARY]
GO
USE [Edu]
GO
/****** Object: Table [dbo].[Products] Script Date: 2021/11/26 15:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[id] [varchar](50) NULL,
[created_at] [varchar](50) NULL,
[description] [varchar](50) NULL,
[name] [varchar](50) NULL,
[updated_at] [varchar](50) NULL
) ON [PRIMARY]
GO
USE [Edu]
GO
/****** Object: Table [dbo].[OLE DB Orders] Script Date: 2021/11/26 15:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OLE DB Orders](
[id] [varchar](50) NULL,
[opportunity_id] [varchar](50) NULL,
[sale_date] [varchar](50) NULL,
[status] [varchar](50) NULL,
[created_at] [varchar](50) NULL,
[is_paid] [varchar](50) NULL,
[promotional_code_id] [varchar](50) NULL,
[marketing_channel_id] [varchar](50) NULL,
[sales_consultant_id] [varchar](50) NULL
) ON [PRIMARY]
GO
November 26, 2021 at 2:14 pm
Could you post the DDL for Opportunities as well? You posted products twice and then orders.
But looking at that, your orders table has no way to related to products UNLESS it is related by opportunities. I imagine you also have a Promotional Code table and a Marketing Channel table and a Sales Consultant table based on the ID columns you have.
Another thing - is there a reason you are making your ID's VARCHAR(50)? Is this a requirement of your system or is there a design reason why you want them to be VARCHAR(50)? INTs are USUALLY used for ID's as comparison (WHERE) and lookup (JOIN) is fast when they are indexed well. VARCHAR may be required in your case, but it does result in larger indexes and slower comparison than an INT. Lookup should be just as fast though when it is indexed well.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 26, 2021 at 2:27 pm
Only the three tables given.
USE [Edu]
GO
/****** Object: Table [dbo].[Opp...] Script Date: 2021/11/26 16:15:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Opp...](
[id] [float] NULL,
[contact_id] [float] NULL,
[created_at] [nvarchar](255) NULL,
[fields] [nvarchar](255) NULL,
[first_activity_at] [nvarchar](255) NULL,
[first_assigned_at] [nvarchar](255) NULL,
[first_assigned_to] [float] NULL,
[first_call_at] [nvarchar](255) NULL,
[last_activity_at] [nvarchar](255) NULL,
[last_assigned_at] [nvarchar](255) NULL,
[last_call_at] [nvarchar](255) NULL,
[lead_source] [nvarchar](255) NULL,
[marketing_channel_id] [float] NULL,
[product_id] [float] NULL,
[tracking_meta] [nvarchar](max) NULL,
[updated_at] [nvarchar](255) NULL,
[user_id] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
November 26, 2021 at 5:07 pm
Looking at your tables, I don't see how you relate "Products" to an opportunity or order.
I WAS going to say that you could related them by ID on Products and Product_ID on Opportunity, but they are different data types which means they MAY not relate well and may even give you errors when you try to join them.
So to answer your original question, "Please advice if the tables have the correct or appropriate columns to create relationships among them?", my opinion they do not relate to each other in any way that I can figure out.
What I see is that none of the tables relate to each other based ENTIRELY on my interpretation of the columns. I see no way to go from a PRODUCT to an OPPORTUNITY or an ORDER in a reliable manner.
Now if you changed your ID's in Order to be VARCHAR(50), then I could see things relating, but as they are now, I do not see a way to relate the tables reliably.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply