|
I have a product that offers a small lead management system. The architecture of product is simple so far. I have tables called 'companies', 'users', 'leads'. Users within a company have CRUD rights on leads. Now companies want their agents to have rights to operate leads. So we can ask companies to add their agent as a user within the company but there is a problem. Each user needs to have a unique email to register. So if one agent serves 5 companies they can't be added as a user by each of them. What should be the ideal way to handle this.
Thought of having separate database for each company so that problem of unique email is resolved but I'm not sure how to manage multiple databases for updates
|
|
|
|
|
You may want to change the "key" to the Users table to be a compound key.
Something like Company ID + user email, this would ensure that within a single company, the email is unique.
|
|
|
|
|
With company id + user email, the sign in logic will fail.
Right now user can sign in with email as its unique.
|
|
|
|
|
Add another table with 3 fields
AgentID PK
UserID (could be the email if it is the PK in the user table) - FK to users
CompanyID - FK to company
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have an ecommerce shop online using php, sql, javascript,ajax and sessions.
I have both guest and members cart options at checkout.
Everything works fine.
I store my cart items in a session currently.
Users can log in or have a guest cart.
Guests cart userids are referenced by the current session id.
members can login and their carts are referenced by their usersids from the database.
The problem is, the session expires after a certain amount of time and so the cart items are lost and the user has to start again.
On doing some research I have found that after the user logs in, I can store his user id in a cookie and I can specify how long that cookie lasts for which is ideal!
I am thinking of changing the code so that I store the items added to the cart in my database tables and simply reference them with the user id ive stored in his cookie.
That way He can shop for ages and not lose his cart and I can send abandon cart emails etc...
I think this would work well as nearly every website uses cookies so people have to have them enabled in their browser these days. I could show a warning message if cookies arent enabled anyway..
What does everyone think about this?
Please note I am not seeking security advice here.
|
|
|
|
|
Let's discuss this in a bit proper detail with the application of performance, security and regulations.
You can store anything that is publicly available on your website, and does not contain any personal or identifying information in a cookie. The reason for this is, that a user might be accessing your website from a public machine or PC and that data might be tracked or stored. Next, for a session you can use this storage to store any information that might identify (because, hey, that is what a session is about, right?) the user and help your website perform an operation quickly—without having to query the database again just to find out their user id, or email address. Do remember that on most frameworks (if not all), session data is sent to the client too.
Now as per regulations, make sure you are requesting permissions from the users to store the cookies on their machines—European regulations require you do that, and then there is GDPR, and etc. etc.
Now as for the database part, this space is expensive and filling it with the content that does not help you in anyway—let us for a moment ignore the analytics, user studies, machine learning for recommender systems, etc.—and they will only take away the space that you should pay for the content that helps you generate some business profits.
What I would recommend here is, store the cart information in the cookies, just reference the URLs of the items they want to purchase. This way, the cart information will be stored locally. This will decrease the overall load on your web servers, and will not cause extra charges in the database storage as well since your users will be storing their interests locally and won't add a cart option in database, and forget it leaving there forever.
One tip I can provide here is to use localStorage APIs, instead of cookies, because cookies also have a round trip from and back to server, adding an overhead on the HTTP request.
Window.localStorage - Web APIs | MDN
In this approach, of course you will end up showing what John wanted to purchase to Johanna as well, and that can be fixed by storing the session id(! Remember, store only the session id not the user id and then verify that the sessions are same for the user—which can be done by some backend algorithm to check if user ids of the sessions are the same  )
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
So lets say I have a table (Ports) with an Identifier and then a compacted not XML field that is semi structured.
select * from Ports --Yields this.
ID Categories
1 Town:York, Street:Main, Phone:712-542-3423
The Categories field might have two fields in it. Town and Street or it might have 15.
I need to get these out into separate fields as soo
ID Town Street Phone
1 York Main 712......
ideas on SQL SQL server 2016 is what we are running.
Ideas?
To err is human to really mess up you need a computer
|
|
|
|
|
Not particularly simple, since different rows could have different "attributes" within the categories column.
Something like this should work:
DROP TABLE IF EXISTS #T;
CREATE TABLE #T
(
ID int,
PropertyName varchar(50),
PropertyValue varchar(50)
);
INSERT INTO #T
(
ID,
PropertyName,
PropertyValue
)
SELECT
T.ID,
LTRIM(LEFT(V.value, CHARINDEX(':', V.value) - 1)) As PropertyName,
LTRIM(SUBSTRING(V.value, CHARINDEX(':', V.value) + 1, LEN(V.Value))) As PropertyValue
FROM
YourTable As T
CROSS APPLY string_split(T.Categories, ',') As V
;
DECLARE @columns nvarchar(max) = STUFF
(
(
SELECT DISTINCT ',' + QUOTENAME(PropertyName)
FROM #T
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
, 1, 1, ''
);
DECLARE @query nvarchar(max) = N'SELECT ID, ' + @columns
+ N' FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T'
+ N' PIVOT (Max(PropertyValue) FOR PropertyName In (' + @columns + N')) As P';
EXECUTE (@query);
DROP TABLE #T; The @query will look something like:
SELECT ID, [Phone],[Street],[Town]
FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T
PIVOT (Max(PropertyValue) FOR PropertyName In ([Phone],[Street],[Town])) As P
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you. I was hoping there was something easier than this. I have like 30 fields to actually deal with. Oh well. Brute force it is. <grin>
To err is human to really mess up you need a computer
|
|
|
|
|
If you have the option of pulling the data into C# it would be a lot easier (likely) to change the data in C# and put it back into SQl.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Depending on how dynamic the table is (I would think Ports would be rather static) I would brute force the categories into separate table(s) and either use Richards pivot or a monstrous view with 30 left outer joins. Then when you get a new Ports just run the brute again to parse the Categories.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I am generating xml from my C# application which look like
<Broker Code=\"ML\" IsAllowEstimate=\"False\" ReviseDate=\"01-16-2018\" BrokerEarnings=\"4Q2017 Pre\" BrokerName=\"BofA Merrill Lynch\"></Broker>
<TickerBrokerStandardDateLineitem>
<Ticker />
<TickerID />
<TickerBrokerStandardDateLineitemValues>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>1Q 2010</StandardDate>
<BRTab>Income Statement</BRTab>
<BRLineItem>NET REVENUES</BRLineItem>
<Action>Extracted</Action>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>329.623</StandardValue>
</TickerBrokerStandardDateLineitemValue>
</TickerBrokerStandardDateLineitemValues>
</TickerBrokerStandardDateLineitem>
</Broker>
i guess due to \" character sql server not able to parse my xml. i tried lot to remove back slash from my xml in c# but failed.
so tell me what kind of changes i can do in my below xquery code as a result \" will not cause any problem
here is my full code
Declare @BrokerBogeyXML xml='
<Broker Code=\"ML\" IsAllowEstimate=\"False\" ReviseDate=\"01-16-2018\" BrokerEarnings=\"4Q2017 Pre\" BrokerName=\"BofA Merrill Lynch\">
<TickerBrokerStandardDateLineitem>
<Ticker />
<TickerID />
<TickerBrokerStandardDateLineitemValues>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>1Q 2010</StandardDate>
<BRTab>Income Statement</BRTab>
<BRLineItem>NET REVENUES</BRLineItem>
<Action>Extracted</Action>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>329.623</StandardValue>
</TickerBrokerStandardDateLineitemValue>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>2Q 2010</StandardDate>
<BRTab>Income Statement</BRTab>
<BRLineItem>NET REVENUES</BRLineItem>
<Action>Extracted</Action>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>454.776</StandardValue>
</TickerBrokerStandardDateLineitemValue>
</TickerBrokerStandardDateLineitemValues>
</TickerBrokerStandardDateLineitem>
</Broker>
<Broker Code=\"ML\" IsAllowEstimate=\"False\" ReviseDate=\"01-16-2018\" BrokerEarnings=\"4Q2017 Pre\" BrokerName=\"BofA Merrill Lynch\">
<TickerBrokerStandardDateLineitem>
<Ticker />
<TickerID />
<TickerBrokerStandardDateLineitemValues>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>1Q 2010</StandardDate>
<BRTab>TER</BRTab>
<BRLineItem>Revenue</BRLineItem>
<Action>Extracted</Action>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>319.338</StandardValue>
</TickerBrokerStandardDateLineitemValue>
<TickerBrokerStandardDateLineitemValue>
<TabName>Consensus Model</TabName>
<StandardDate>2Q 2010</StandardDate>
<BRTab>TER</BRTab>
<BRLineItem>Revenue</BRLineItem>
<Action>Extracted</Action>
<StandardLineItem>Net Revenue</StandardLineItem>
<StandardValue>445.271</StandardValue>
</TickerBrokerStandardDateLineitemValue>
</TickerBrokerStandardDateLineitemValues>
</TickerBrokerStandardDateLineitem>
</Broker>
'
SELECT
d.v.value('../../../@Code[1]', 'varchar(MAX)') AS BrokerCode,
d.v.value('../../../@IsAllowEstimate[1]', 'varchar(MAX)') AS IsAllowEstimate,
d.v.value('../../../@ReviseDate[1]', 'varchar(MAX)') AS ReviseDate,
d.v.value('../../../@BrokerEarnings[1]', 'varchar(MAX)') AS BrokerEarnings,
d.v.value('../../../@BrokerName[1]', 'varchar(MAX)') AS BrokerName,
d.v.value('(TabName/text())[1]','VARCHAR(MAX)') AS TabName,
d.v.value('(StandardDate/text())[1]','VARCHAR(MAX)') AS StandardDate,
d.v.value('(BRTab/text())[1]','VARCHAR(MAX)') AS BRTab,
d.v.value('(BRLineItem/text())[1]','VARCHAR(MAX)') AS BRLineItem,
d.v.value('(Action/text())[1]','VARCHAR(MAX)') AS Action,
d.v.value('(StandardLineItem/text())[1]','VARCHAR(MAX)') AS StandardLineItem,
d.v.value('(StandardValue/text())[1]','VARCHAR(MAX)') AS StandardValue,
'192.16820.25' AS IPAddress
FROM @BrokerBogeyXML.nodes('/Broker/TickerBrokerStandardDateLineitem/TickerBrokerStandardDateLineitemValues/TickerBrokerStandardDateLineitemValue') AS d(v)
getting this error
XML parsing: line 2, character 14, A string literal was expected
when i change this one
<Broker Code=\"ML\" IsAllowEstimate=\"False\" ReviseDate=\"01-16-2018\" BrokerEarnings=\"4Q2017 Pre\" BrokerName=\"BofA Merrill Lynch\"></Broker> to
<Broker Code="ML" IsAllowEstimate="False" ReviseDate="01-16-2018" BrokerEarnings="4Q2017 Pre" BrokerName="BofA Merrill Lynch" >
then my above code works fine.
please give me solution.
|
|
|
|
|
Hi again!
I have a doubt:
Let's say I have a tasks table:
---------------------
Tasks Table
---------------------
id : INT (PK)
idProject : INT
idUser : INT
idTaskType : INT
idInvoice : INT
date : DATETIME
start_time : DATETIME
end_time : DATETIME
pricePerHour : FLOAT
notes : LONGTEXT
---------------------
Then I have a types of tasks table:
---------------------
Tasks types table
---------------------
id : INT (PK)
name : VARCHAR(45)
---------------------
Then I have a types of tasks table:
---------------------
Tasks types table
---------------------
id : INT (PK)
name : VARCHAR(45)
---------------------
Also a users table
---------------------
Users table
---------------------
id : INT (PK)
idGroup : INT
name : VARCHAR(45)
---------------------
And a projects table
---------------------
Projects table
---------------------
id : INT (PK)
idCustomer : INT
notes : VARCHAR(200)
---------------------
And a reference prices table
---------------------
Reference prices table
---------------------
idProject : INT (PK) -> foreign key to Id at projects table
idUser : INT (PK) -> foreign key to Id at users table
idTask : INT (PK) -> foreign key to Id at tasks types table
pricePerHour : FLOAT
---------------------
My idea is to have specific prices depending on the project, the user and the task to be performed.
As you can see there are two pricePerHour fields (one in the "Reference prices table" and one in the "tasks" table).
I've found that in some projects prices change during the project... so it would be able to change the prices on the fly...
What do you think if my GUI uses the reference price as basis to assign the task price?
That way I would be able to change the price of each task if needed (renegotiating...).
Is there any better way to do that?
modified 19-Jul-19 7:13am.
|
|
|
|
|
Based on that design, you can't have an entry in the reference prices table until you have an entry in the tasks table. So you wouldn't be able to use the reference price (that doesn't exist yet) to set the price when you create the task.
Wouldn't it make more sense for the reference prices table to link to the task types table?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
WHOA! yes, that's a typo, I've done like this in the MySQL Workbench...
I did not wanted to paste all the SQL script here... maybe too much...
Knowing you are right and that I had like this in my original design, do you think it can work well?
PS: already modified the original post.
Thanks Richard!
|
|
|
|
|
It sounds reasonable. We use a slightly more complicated version of something similar in our airport management software. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Slightly you say...
It's been years since the last time I designed a database and it was at school, so well... I preferred to ask for advice here before...
Thank you for checking it!
 
|
|
|
|
|
Hello All,
First time using a database of any kind. I am using SQLite with a VB.net application. Currently I store/retrieve data from an excel workbook and I want to replace that method with the database.
I have the code working to generate the database tables but want some expert opinions on best practices when laying out my tables. In the workbook I have a sheet for each drawer in several large containers 10 containers with 100 drawers each. Each drawer holds bins of different parts for a specific machine so each one is unique. So my excel sheet looks like this:
"Sheet Name = Container_1_Drawer_1"
"Sheet Row 1 = Pins"
"Sheet Row 2 = Clips"
...
"Sheet Row 100 = 1/4-20 x 1 Bolt"
My first though is to have a table for the drawer that is referenced by a table of containers but having no exp with databases I am not sure if this is the best approach or if I could do something more elegant.
Thanks in advance.
|
|
|
|
|
My gut feel says that you need to have a table for the parts themselves - I doubt there is only one machine that uses Pins or Clips or that size bolt.
Part
- PartID - a unique identifier (int or guid)
- PartName
- Other information as required Parts go into Bins and I'm assuming here that each bin can only contain one type of Part so
Bin
- Bin_ID - unique identifier
- Drawer_ID - Foreign key to Drawer table
- Part_ID - Foreign key to the Part table
- Information about the bin e.g. number of parts in this bin Bins go into Drawers but note that the Drawer table doesn't reference the Bins, the Bins reference the Drawer, it's "owner" if you like. The same applies to the Drawer referencing it's Container
Drawer
- Drawer_ID
- Container_ID - Foreign key to Container table
- Other info about the Drawer e.g. Machine reference?
Container
- Container_ID
- Other info about the container e.g. location It's very easy to over-engineer database design. For example I'm tempted to introduce a Machine table but that's probably over-doing it.
You might find these links helpful too:
Database Design Tutorial for Beginners - William Vincent[^]
How to Design a SQL Database[^]
|
|
|
|
|
|
Thank you both. I looked at the links and they will help a lot. I think I trying to make it harder than it needs to be..I guess that is the engineer in me. 
|
|
|
|
|
Hi all,
I'm thinking on making a small application to keep track of my working hours, billable or not...
I'd install it in my NAS to be able to access it from everywhere.
The database will be a MySQL database.
I'll do the programming in PHP (don't know any other thing for web).
I've been far away from databases for a long time and wanted to make a question about the right data structure...
I plan to have:
------------------
Table 1: CUSTOMERS
------------------
(PK) ID : autonumeric
Name : string
Address : string
------------------
Table 2: PROJECTS (1 customer can have N projects)
------------------
(PK) ID : autonumeric
Customer ID : number
Description : string
------------------
Table 3: TASKS_IN_PROJECT (1 project will have N tasks).
------------------
(PK) ID : autonumeric
Project ID : number
Person ID : number
Description : string
Date : timeDate
Start time : time
End time : time
Price per hour : number
------------------
Table 4: TASKS (N Tasks_In_Table will have 1 task).
------------------
(PK) ID : autonumeric
Description : string
------------------
Table 5: PEOPLE (N Tasks_In_Project will have 1 person).
------------------
(PK) ID : autonumeric
Person name : string
------------------
Table 6: EXPENSES_IN_PROJECT (1 project will have N expenses).
------------------
(PK) ID : autonumeric
Project ID : number
Note : string
Date : timeDate
Cost : number
------------------
Table 7: EXPENSES (N Expenses_In_Project will have 1 Expense).
------------------
(PK) ID : autonumeric
Description : string
My idea is having this database structure and then make a small PHP web page that would allow me to introduce customers, projects assigned to customers, tasks and persons for each project (those should be available from a combobox selecting from a list of available tasks (tasks table) and people (people table)), and also I would like to be able to add expenses to the project too in the same way... those expenses should be selected from a list coming from the Expenses table...
Do you think this is a good structure?
Have I done something terrible?
My objectives are:
1. storing all the worked hours.
2. being able to get some reports (total hours in a project, lists of tasks done...)
Thank you very much for your time and advice!
|
|
|
|
|
Joan M wrote: Have I done something terrible?
No, it's not VB6 nor JavaScript.
Joan M wrote: Do you think this is a good structure?
Decent
Just a few questions;
Joan M wrote: (N Tasks_In_Table will have 1 task)
I don't see that table's structure?
Joan M wrote: Price per hour : number
Are you going to lookup a price and enter it manually for every task? How about a table with base-tasks and pricing to choose from? Perhaps with an additional column for discounts or extra?
Also, you use autonumbers for your keys; That means you can enter a customer twice, without problems. I'd prefer a unique constraint on the autonumber, and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project. It'd also mean that a project would require a unique constraint on its autonumber for quick reference, but also a decent PK; the combination of the username and the projectname.
--edit
Free tip; if you're unsure about a structure, populate your example with mock-data and try some queries
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Quote: and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project.
It's nice until you end up with two customers with the same name!
In my case I also can't use part of the address as I have a Father/Son scenario with the same name living at the same address (besides, people do move house occasionally).
So we have autonumbers - the onboarding process does the necessary checks for duplication
|
|
|
|
|
CHill60 wrote: It's nice until you end up with two customers with the same name!
You, like the computer, and the cashier, need to be able to distinguish between the two, even in a listview. Wouldn't want to bill the father for the son's purchases
CHill60 wrote: So we have autonumbers - the onboarding process does the necessary checks for duplication
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
