I am trying to build a query from a table (Tbl_XYZ) that will give me the information for the primary key all in one row rather then creating dups for that primary key and showing up with multiple rows. Example below.( this is just a sample) | Account Number(PK) | System_Name | Table_Name| |:---- |:------: | -----: | | 012345 | DW | Customer | | 012345 | DW | Sales | | 012345 | DW | Address | | 012345 | AWS | aws_Cust | | 012345 | AWS | aws_product | I was thinking about creating 2 separate tables for System_Name and Table_Name but it would still give me values in different rows since the table names are different. My expectation : ( Of course, I will have to create separate queries for different system names (DW and AWS) | Account Number | System_Name | Table_Name| |:---- |:------: | -----: | | 012345 | DW | Customer / Sales / Address | | 012345 | AWS | aws_Cust / aws_product | Any help with this would be greatly appreciated. I am using MS Access 2013 Thanks.

I'm using Xampp phpmyadmin for the first time... So I've got the basics of the database. Customers, hotels, rooms etc. The customer can chose a check-in and check-out date. An admin can class a date as available/not. My question, is how would I get the database to automatically update the dates as unavailable? When I've tried, it makes that date unavailable for every room in every hotel. (Still a newbie at all this, so sorry if this doesn't make any sense!) Attached a rough draught of my ERD for reference.

My question is more from the schema design and of further performance of the DB. I'm designing a DB schema which will store strings primarily. Actually, users work on these strings, as normal mortals, in macro-enabled Excel workbooks and there, they have one column specifically designed to store keywords so they can find words within a second column quickly. Some of these words are obviously deduced. For example, if column 1 is the keywords column and column 2 is the text column, column 2 could say "information required for feature description" and, its keywords in column 1 would be "information", "feature" and "description". So far so good, common sense, but there are texts that have, for example, "N", "S", "W", "E", as the four points of a compass but in the keyword column, instead of having "North", "South" and so on, the keyword for all is just "compass". We could ask for a keyword change but it would be a little troublesome, so my question is, is it worth saving these keywords in the schema, whether there's a table for them or not, or is it better to get the keyword as the result of a query from the texts stored? We know working with strings is difficult, would these affect performance in the future? BTW, if it helps, I'm thinking of using PostgreSQL.

I'm developing a marketplace-style application that allows users to upload purchasable digital items -> the public purchases these items -> and for my application to pay the users (owners of items) their owed funds via PayPal Payouts API on a daily basis. I'm struggling with how best to calculate/store the owing balance, and how to map the individual purchase transaction records to the concept of a "payout" (when we send owed funds to the user). Schema so far: **User** - id - name - createdAt - etc. **Purchasable Item** - id - user_id (owner) - price - createdAt - etc. **Transaction** - id - type ("purchase" or "payout") - status (depending on PayPal response. COMPLETED, FAILED, REFUNDED etc.) - value (integer (lowest demomination of currency). Positive integer for purchase, negative for a payout). - purchasable_id (For "purchase" transactions, reference the ID of the purchasable item that was purchased) - transaction_fee - createdAt - **payout_id** (?) The ID of the payout (below) this purchase is included in. Not sure about this. This won't be known at the time of the transaction, so it would need to be updated to store it and I'm not sure how to know which transaction will belong in which payout? **Payout** Not sure about this. Feels like a duplicate of a payout transaction entry, but I want a way to store which purchase transactions were paid out in which payouts. - id - status (depending on PayPal response to Payout API webhook. COMPLETED, FAILED, REFUNDED etc.) - createdAt **Logic:** This is where I need the most help. **CRON job. Every 24hrs:** - Calculate each users balance by summing the payout_balance_change fields of the Transactions table. i.e balance isn't stored, it's always calculated. **Is that a good idea?** - Insert a row into "Transactions" of type "payout" with a negative "payout_balance_change". i.e. subtracting the amount we will send in the payout, zeroing their balance in the Transactions table. - Insert a row into "Payouts" table that stores the details of the payout attempt. **Problems:** - **How will I know which purchase transactions belong to each payout cycle** (so I can then store the payout_id in those transaction records). I could use the date of the transaction, and each payout could be for the 24hr period prior to the CRON job? I'm flexible on this and not sure what the most robust logic would be. Any advice on how best to structure this, or links to similar projects would be greatly appreciated. Thank you!

Is it possible to create a table that is built from an inner join of two other tables with additional columns, but where the rows are kept updated when the inner join is re-run? Currently using postgresql 17. So far I have the following tables:

CREATE TABLE public.outcomes (
    id integer NOT NULL,
    paper_id integer NOT NULL,
    outcome text NOT NULL
);


CREATE TABLE public.papers (
    id integer NOT NULL,
    paper_id text NOT NULL
);


CREATE TABLE public.predictors (
    id integer NOT NULL,
    paper_id integer NOT NULL,
    predictor text NOT NULL
);


COPY public.outcomes (id, paper_id, outcome) FROM stdin;
1	1	outcome 1
2	1	outcome 2
3	2	outcome 3
\.


COPY public.papers (id, paper_id) FROM stdin;
1	paper a
2	paper b
\.


COPY public.predictors (id, paper_id, predictor) FROM stdin;
1	1	predictor 1
2	1	predictor 2
3	2	predictor 1
4	2	predictor 2
\.


ALTER TABLE ONLY public.outcomes
    ADD CONSTRAINT outcomes_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.papers
    ADD CONSTRAINT papers_pkey PRIMARY KEY (id);


ALTER TABLE ONLY public.predictors
    ADD CONSTRAINT pk_predictors PRIMARY KEY (id);


ALTER TABLE ONLY public.outcomes
    ADD CONSTRAINT outcomes_paper_fkey FOREIGN KEY (paper_id) REFERENCES public.papers(id);


ALTER TABLE ONLY public.predictors
    ADD CONSTRAINT predictors_paper_fkey FOREIGN KEY (paper_id) REFERENCES public.papers(id);

I have tried creating a materialized view based on a query that joins all three tables, and I can create triggers to refresh the materialized view when the outcomes or predictors tables are added to.

select pa.paper_id, ou.outcome, pr.predictor
from predictors pr
inner join outcomes ou on ou.paper_id = pr.paper_id 
inner join papers pa on pa.id = ou.paper_id;

But I can't find a way to create a table from this query that: - lets me add new rows to the child table when I add rows to either the outcomes or predictors tables, and - lets me store new data in additional columns |paper_id|outcome |predictor | new column here | |--------|---------|-----------|-----------------| |paper a |outcome 1|predictor 1| |paper a |outcome 2|predictor 1| |paper a |outcome 1|predictor 2| |paper a |outcome 2|predictor 2| |paper b |outcome 3|predictor 1| |paper b |outcome 3|predictor 2| Any pointers or suggestions of what to investigate welcomed.

Let's say I have a user table and a card table, like this: user: user_id card: card_id user_id card_text card_order As of right now, a user can have up to five cards. Users should also be able to re-arrange their cards on the frontend. So let's say a user had four cards, with the text for each one being "Apple", "Banana", "Carrot", "Donut", respectively. The users should be able to re-arrange them and this change should be persisted into the database. So for example, an example schema where the order was Apple, Carrot, Donut, and Banana would look like this: card_id user_id card_text card_order ------------------------------------------ 23 1 Apple 1 53 1 Banana 4 43 1 Carrot 2 58 1 Donut 3 Is there a better way to do this? My issue is with updating them. Let's say the user drags the Banana card to the #2 slot. Then Apple would stay 1, Banana would become 2, Carrot would become 3, and Donut would become 4. I'm not exactly sure what sort of SQL code I would have to execute to perform that sort of logic. Maybe something like.. UPDATE card SET card_order = card_order + 1 WHERE user_id = $1 AND card_order >= $2; UPDATE card SET card_order = $2 WHERE user_id $1 AND card_id = $2; Something like that? Not sure, but I feel like there is a better way? An alternative is to have something like a card_order column in the user table which contains the ordering like: [ 23, 43, 58, 53 ], which may be easier to re-arrange, but then I have to do a JOIN with the user table every time I want to get the cards in the correct order. In addition, I'm not sure how well foreign keys would work in that situation? I fear the array could accidentally get corrupted with old card ids if the referential integrity was not checked.

I am experimenting with a proposed principle (the NKTg Law) which relates a planet’s orbital distance, orbital velocity, and mass. Normally I would code this in Python, but here I wanted to see how the same calculation can be expressed and verified in SQL using NASA’s data for the 8 planets (as of 30–31 Dec 2024). Method Steps: 1. From NASA data, take: x = distance from the Sun (km) v = orbital velocity (km/s) m = mass (kg) 2. Compute: p = m * v NKTg1 = x * p m_interp = NKTg1 / (x * v) delta_m = m - m_interp 3. Compare m_interp with NASA’s given mass m. Input Data (NASA values) Mercury: Vx_km = 69,817,930 v_km_s = 38.86 m_kg = 3.301e+23 Venus: x_km = 108,939,000 v_km_s = 35.02 m_kg = 4.867e+24 Earth: x_km = 147,100,000 v_km_s = 29.29 Vm_kg = 5.972e+24 Mars: x_km = 249,230,000 v_km_s = 24.07 m_kg = 6.417e+23 Jupiter: x_km = 816,620,000 v_km_s = 13.06 Bm_kg = 1.898e+27 Saturn: x_km = 1,506,530,000 v_km_s = 9.69 m_kg = 5.683e+26 Uranus: x_km = 3,001,390,000 v_km_s = 6.80 m_kg = 8.681e+25 Neptune: x_km = 4,558,900,000 v_km_s = 5.43 m_kg = 1.024e+26

SQL Setup (PostgreSQL)
CREATE TABLE planets (
    planet TEXT PRIMARY KEY,
    x_km BIGINT,
    v_km_s NUMERIC,
    m_kg NUMERIC
);

INSERT INTO planets (planet, x_km, v_km_s, m_kg) VALUES
('Mercury', 69817930,     38.86, 3.301e23),
('Venus',   108939000,    35.02, 4.867e24),
('Earth',   147100000,    29.29, 5.972e24),
('Mars',    249230000,    24.07, 6.417e23),
('Jupiter', 816620000,    13.06, 1.898e27),
('Saturn',  1506530000,    9.69, 5.683e26),
('Uranus',  3001390000,    6.80, 8.681e25),
('Neptune', 4558900000,    5.43, 1.024e26);

Query to Verify the Law

SELECT
    planet,
    m_kg,
    (m_kg * v_km_s) AS p,
    (x_km * m_kg * v_km_s) AS NKTg1,
    ((x_km * m_kg * v_km_s) / (x_km * v_km_s)) AS m_interp,
    m_kg - ((x_km * m_kg * v_km_s) / (x_km * v_km_s)) AS delta_m
FROM planets;

Example Output

Mercury:

m_kg = 3.301e+23

p = 1.283e+25

NKTg1 = 8.952e+32

m_interp = 3.301e+23

delta_m = 0

Venus:

m_kg = 4.867e+24

p = 1.704e+26

NKTg1 = 1.857e+34

m_interp = 4.867e+24

delta_m = 0

Earth:

m_kg = 5.972e+24

p = 1.750e+26

NKTg1 = 2.571e+34

m_interp = 5.972e+24

delta_m = 0

Mars:

m_kg = 6.417e+23

p = 1.545e+25

NKTg1 = 3.847e+33

m_interp = 6.417e+23

delta_m = 0

Jupiter:

m_kg = 1.898e+27

p = 2.478e+28

NKTg1 = 2.024e+37

m_interp = 1.898e+27

delta_m = 0

Saturn:

m_kg = 5.683e+26

p = 5.509e+27

NKTg1 = 8.297e+36

m_interp = 5.683e+26

delta_m = 0

Uranus:

m_kg = 8.681e+25

p = 5.902e+26

NKTg1 = 1.772e+36

m_interp = 8.681e+25

delta_m = 0

Neptune:

m_kg = 1.024e+26

p = 5.558e+26

NKTg1 = 2.533e+36

m_interp = 1.024e+26

delta_m = 0

Discussion The interpolated mass m_interp always equals the NASA mass m. The difference delta_m is 0 for all 8 planets. This indicates a consistent relationship between orbital distance, velocity, and mass, which supports the proposed NKTg Law.

> Create a database schema design based on the following (simplified) requirements of the COMPANY Database: > > - The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations. > - Each department controls a number of PROJECTs. Each project has a unique name, unique number and is located at a single location. > - We store each EMPLOYEE’s social security number, address, salary, gender, and birthdate. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. > - Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, gender, birth date, and relationship to the employee. > > Each entity should contain a name, attributes, PK/FK, and Datatypes. Remember, every time you encounter a many-to-many relationship, you need to add a bridge table. My ERD so far: How are locations and the manager/supervisor part supposed to be put into the ER diagram? Maybe they should be put in via bridge tables.

Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value For example, let's say I have a record with the value "link" under thetag_type field and "https://dba.stackexchange.com " under the tag_value field. Now, how would I approach this for auditing purposes? I want to only INSERT into this table when one of the tag_value fields corresponding to a tag_type has changed, because it would not make sense to add redundant records to this table. Is this a database design problem I'm having, or is there advanced SQL I'm unaware of that would accomplish this? Or, rather, would the Java I am getting these values from initially be the place to handle such a thing? Example of how I would do this in Java (psuedo code): var newTag = extract the tag's value from my webpage; var tag_value = SELECT tag_value from html_tag WHERE tag_type = 'link' if (new_tag != tag_value) then (INSERT INTO html_tag)... Should I handle this at the database level, or java level, like directly above?

I have a table with name app_client with the below fields: > id, user_id, name, age, phone, gender, **related_entities_json (long text datatype)**, > created_at, updated_at I have 200k+ records in this table and I found that the size is mainly increased (7 GB ) due to related_entities_json field. So I want to know if should I move related_entities_json to a separate table? And then access its data when needed? I don't want to open another question for this so I would also like to know if this will increase performance during fetching/inserting data. Entry in related_entities_json from a row: https://pastebin.com/y4XDCK4Y

We have a small local computer that we use as a temporary database backup to automatically recover data from sensors when we lost connection to the main cloud server or when we have to make maintenance to this server. Due to the small space disk available for data (32go) the idea was to keep last 3 month of sensors data and delete oldest entries. Here is the description of the database:

CREATE TABLE IF NOT EXISTS machine (
  Nom varchar(50) NOT NULL,
  Site smallint(5) unsigned NOT NULL,
  Emplacement smallint(5) unsigned DEFAULT NULL,
  ID smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (ID),
  UNIQUE KEY Nom (Nom),
  KEY FK__site (Site),
  CONSTRAINT FK__site FOREIGN KEY (Site) REFERENCES site (ID)
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS mesure (
  Machine smallint(5) unsigned NOT NULL,
  Date datetime NOT NULL,
  Valeur decimal(13,2) NOT NULL,
  PRIMARY KEY (Machine,Date),
  KEY Date (Date) USING BTREE,
  CONSTRAINT FK__machine FOREIGN KEY (Machine) REFERENCES machine (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS site (
  Nom varchar(50) NOT NULL,
  ID smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

In a first time I thought that making periodic deletion will be sufficent but I discovered that innoDB does not release space disk to the OS, and now the disk partition is full (filled in 4 month). More, these delete are bit slow. After some researches I found that partitions can be the solution to my issue but it seems that it's not possible to make partition with a foreign key inside the table. Tried this code, just for testing purpose :

ALTER TABLE mesure
PARTITION BY RANGE(UNIX_TIMESTAMP(Date))
(
	PARTITION START VALUES LESS THAN (UNIX_TIMESTAMP("2021-10-01 00:00:00")),
	PARTITION MONTH1 VALUES LESS THAN (UNIX_TIMESTAMP("2021-11-01 00:00:00")),
	PARTITION MONTH2 VALUES LESS THAN (UNIX_TIMESTAMP("2021-12-01 00:00:00")),
	PARTITION MONTH3 VALUES LESS THAN (UNIX_TIMESTAMP("2022-01-01 00:00:00")),
	PARTITION END VALUES LESS THAN MAXVALUE
	
);

That results to :

/* Erreur SQL (1217) : Cannot delete or update a parent row: a foreign key constraint fails */

I'm now run out solutions to make this solution viable on our hardware. Any idea on how I can manage this ?

I am developing an e-commerce store which has cosmetic products. Now the challenge I am facing is that seller has a set of nail polish/ lipsticks boxes etc and those items inside can be sold individually as a single product.
There is no issue creating a single product table and put everything there but I don't want seller to keep adding same type of product over and over again which just differs in color shade.
I tried to group them by creating a product table having product name, category, brand and putting individual products inside as a product_variants table which has price, sku, attributes and everything but that made a lot of confusion even for me as I am calling for variants and showing them as individual product on main site, even on admin panel when looking at product list, actual products are inside of product list shown.
My question is How can I make this simpler for me and for seller also. Is there any other way for simplifying this kind of relationship or should I just create a parent table/group and link back all products to them for easy read.
Thanks

Long time reader but first post! I am designing a database that requires me to be able to calculate the inventory available for a perishable good on any given day. In my case, it is for a farm. Farmers can add inventory updates for any present or future date (today I have 4 available, tomorrow I have 5 more available). Farmers also set how long their inventory lasts before it expires (this is per farm, not per item). Order items inventory purchased (order items) must be a part of an order that has a fulfillment date before the inventory expires. The inventory cannot be purchased for a fulfillment date after its expiration. More inventory cannot be purchased than is available. Currently, I have a job that runs every night that removes expired inventory (inserts it into inventory_expired), by calculating the total amount of inventory that expires today or previously and subtracts the quantity in orders placed and the inventory that was already expired. I feel like this isn't the right way to go about it. My approach lets me accurately calculate the inventory up until the current day, and in the past accurately, but can't calculate future inventory properly, because the future date doesn't know what inventory has expired. Maybe I am making this too complicated. Is there a better way to structure what I am trying to do? Or is there a function I can write that would be able to calculate the available inventory from my current design? Here is what is relevant out of my current tables:

CREATE TABLE organizations (
  id int NOT NULL,
  PRIMARY KEY(id)
);
CREATE TABLE farms (
  id int NOT NULL,
  organization_id int NOT NULL,
  inventory_expiration_days int NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (organization_id) REFERENCES organizations (id)
);


CREATE TABLE inventory (
 id int NOT NULL,
 organization_id int NOT NULL,
 farm_id int NOT NULL,
  primary key(id),
 FOREIGN KEY (organization_id) REFERENCES organizations (id),
 FOREIGN KEY (farm_id) REFERENCES farms (id)
);

CREATE TABLE inventory_updates (
  id int NOT NULL,
  date date NOT NULL,
  inventory_id int NOT NULL,
  quantity_added int,
  quantity_removed int,
  FOREIGN KEY(inventory_id) REFERENCES inventory (id)
);

CREATE TABLE inventory_expired (
  id int NOT NULL,
  inventory_id int NOT NULL,
  date date NOT NULL,
  quantity_expired int NOT NULL,
  FOREIGN KEY(inventory_id) REFERENCES inventory (id)
);

CREATE TABLE orders (
  id int NOT NULL,
  fulfillment_date date NOT NULL,
  seller_org_id int NOT NULL,
  primary key(id),
  FOREIGN KEY(seller_org_id) REFERENCES organizations (id)
);

CREATE TABLE order_item (
 id int NOT NULL,
  order_id int not null,
 inventory_id int NOT NULL,
 quantity int NOT NULL,
 FOREIGN KEY(inventory_id) REFERENCES inventory (id),
  FOREIGN KEY(order_id) REFERENCES orders(id)
);

And here is my current function, which works for calculating inventory for dates up to today but not in the future. I put a hard coded date in (2021-02-21) for now.

SELECT t.inventory_id, 
COALESCE(t.total_quantity, 0) as total_quantity, 
COALESCE(exp.quantity_expired_previously, 0) quantity_previously_expired, 
COALESCE(sold.quantity_sold, 0) as quantity_sold,
COALESCE(t.total_quantity, 0) - COALESCE(exp.quantity_expired_previously, 0) - COALESCE(sold.quantity_sold, 0) as quantity_available
FROM
(SELECT u2.inventory_id as inventory_id, SUM(COALESCE(u2.quantity_added, 0) - coalesce(u2.quantity_removed, 0)) as total_quantity
  FROM inventory_updates u2 
  LEFT JOIN inventory i2 on i2.id = u2.inventory_id
  LEFT JOIN farms f2 on f2.organization_id = i2.organization_id
  WHERE u2.date <= ('2021-02-21'::date)
  GROUP BY u2.inventory_id) t
LEFT JOIN (
  SELECT e.inventory_id, COALESCE(SUM(e.quantity_expired), 0) as quantity_expired_previously
  FROM inventory_expired e
  WHERE e.date <= ('2021-02-21'::date)
  GROUP BY e.inventory_id
) exp on exp.inventory_id = t.inventory_id
LEFT JOIN (
  SELECT i.inventory_id, SUM(i.quantity) as quantity_sold
  FROM order_item i
  LEFT JOIN orders o on o.id = i.order_id
  LEFT JOIN farms f on f.organization_id = o.seller_org_id
  WHERE o.fulfillment_date < ('2021-02-21'::date + (f.inventory_expiration_days * interval '1 day'))
  GROUP BY i.inventory_id
) sold on sold.inventory_id = t.inventory_id

I was wondering if some sort of union on all of the updates that occur to the inventory quantities could work better, but also think I am going to run into problems when I have lots of rows, and any sort of sum over everything is going to take a long time. Basically, I am pretty stuck.

I have a database where I am trying to figure out how to join a table that contains an object called ModelType. The calibration table contains two fields (EffModel and BkgModel) that are based on a ModelType. I cannot figure out how to join these two fields. Below is an image of the stucture. I am trying to make sure that if I were ever to update one of the model types, that I would not have to update the reference Calibration. I thought about creating a table called EffModelType and BkgModelType, which would separate the two fields depending on the same table but cannot for the life of me think of what would go in it. Updated Info: I may have misspoken about EFFModel and BkgModel Being based on Model type. Essentially an example table model type is as follows: ModelTypeID | ModelName | ModelNumber +++++++++++++++++++++++++++++++++++++++++++ 1 | Linear | 1 2 | Exponential | 2 and the EFFModel will be assigned a model type such as 1 (linear) and the BkgModel will also be assigned a model type, such as 2 (Exponential). Also the ModelNumber is an identifier from another application that will eventually be deleted so it can be ignored.

I am very much a beginner and taking a class on databases. My assignment involves using Crow's Foot notation to represent the following tables: This is my design for it so far. I am nowhere near done but am I at least on the right track? I would have to use a composite entity to resolve the M:N relationships shown with these tables, right? (Those M:N relationships being Employee to Plan and Benefit to Plan) Please be forgiving of my work haha. I really appreciate all of the help and insight! Hope you all have a good one wherever you are!

I have a table with user info (each user has a unique SERIAL ID), a posts table (each post also has a unique SERIAL ID, and references the ID of the user who posts it), a following table with two columns, each is a user id. Then there is a viewed table with a user id column and a post id column, so a user won't see a post twice. I currently have an API endpoint /feed and it performs two PostgreSQL commands. The first one gets the last five most recent posts from the accounts that a user follows, but I want the user to not see a post twice, so next I store the ID's of the posts that are sent in the viewed table. I want this to be in one single PostgreSQL command. How could I achieve this?

I'm not necessary sure if the "wizard" is the correct term for it, but essentially what I have is a form that have a specific set of questions that a user must answer in order to determine which option is the best solution for them. I have the following tables: - Question - Answer - QuestionAnswer - WizardOption What I'm having an issue with is creating the relationship between QuestionAnswer and WizardOption. (I'm not sure if I'm just over thinking it; I'm new to designing a database structure). **QuestionAnswer Table** - QuestionID INT - AnswerID INT **WizardOption Table** - WizardOptionID INT - WizardOptionText VARCHAR(25) To connect the two, is it ideal to create another table, like so: **WizardOptionByAnswers Table** - WizardOptionID INT - Answers VARCHAR(100) And then in the Answers column I would store the correct order of answers needed to match with the WizardOptionID. +----------------------------------+ | WizardOptionID | Answers | +----------------------------------+ | Option1 | 1,2,1,2,3,1,1,1 | | Option2 | 2,1,2,2,1,2,1,1 | | Option3 | 2,2,2,1,3,1,1,2 | +----------------------------------+ Not sure if that is the correct way to do this. Please let me know if more information is needed.

So i'm building a database for an app/website. It's essentially a e-commerce marketplace/platform where a brand can sign up to upload their products and customers can also then sign up to purchase any products from any various brands. A brand and or employee cannot be a customer A customer cannot be a brand This is the structure I have so far, but it seems to me that from this diagram it implies that a user can be a customer, brand and brand employee at the same time. Is there a better optimal structure for this?

I'm developing an internal tool (as an intern). We deal with a lot of hardware, which is all custom and managers have a hard time knowing who is developing on what machine. The three main things to track for this project will be Hardware, Projects and the Employees. I'm struggling with how to deal with the managers. I was struggling with databases and of course I have to design one in my first internship. Any and all suggestions are greatly appreciated. Here is my current ERD: Here is an excerpt from the design document for context: >Tool Features > >1. Track gaming hardware: >Sort or search by serial no, vendor, platform, type. >2. Track project pool: >See employees and VLT’s assigned by project. Emails are directly in the web >page. >3. Track lab hardware: >Sort or search by serial no, vendor platform and type. >

We have one keyword table and different tables connecting to the keyword table as the interface table (e.g **question_keyword**, **answer_keyword**). We would like to create one master keyword table so when the user searches for a word it can find the word in question table or answer table and retrieve information from that table. We thought of having one master_table(id, question_id, answer_id, keyword_id). Is this design correct? and if we have more tables (means more columns in master_table), how would be the performance (since it might make the master_table as sparse matrix)?