Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

What kind of Database structure / data model should I use?

My goal is to build a database that stores 3 lists of tags and corresponding data for each tag (each list about 10,000 tags with each tag having 30 fields of data). I have to constantly import these 3 lists every week or so and compare them. Each import will result in unchanged tags, new tags, lost tags, modified tags, and unchanged/new/lost/modified data.

I plan to keep each of these lists in separate tables. Where I need a little help is whether or not I need a 4th table as a “Master List”. I could write a program to verify tags and write the results to the Master List table.

The end result will be a client application that I build that is used to browse the database. The reason I think I need the 4th table (Master List) is to store the results of comparison routines so that the client application doesn’t have to run these processes on the fly.  Also, I’m unsure as to whether I should write duplicate tags to the Master List or to only write unique tags and flag them as having a duplicate in one of the Lists.

Attached are a few tables showing the examples of the data structure. Am I on the correct path? I want to make sure I start this project with a solid foundation...

P.S. I do not have great skills with databases. I can navigate and build queries but that’s about the extent of it. I am relatively proficient with programming.

 database table examples AND end-user report example
1 Solution
You didn't describe your application at all, so noone knows the requirements nor can we tell you if you should do anything. All you've done is described something you decided to do, but not what problem you are solving, so any answer is as good as the next. So if you want more help, describe your requirements.

I can tell you, though, that when data has the same structure, it usually goes into the same table. 3 tables of the same structure is usually a "red flag" right away.

Also, if you did have 3 tables of same structure for valid reasons, you can create a view of the tables to represent a master list with UNION

create view v$masterlist as
select * from t1
select * from t2
select * from t3
Your idea seems needlessly complex to me, but I could be misunderstanding your requirements.

From what I gather you need a report of all tags that exist and the status of those tags. This could be generated any one of a number of ways. A master table seems like a reasonable method.

Perhaps if you give some more detail...
- this is what i would suggest:

1-create 2 table: list_master and list_detail
2- load all list1, list2 and list3 data into one table called list_detail
3- in the same list_detail table, create a groupid column to differentiate each tag is belong to which listgroup (1 or 2 or 3) for your Browsing App report purpose.
4- in the list_master table, i would follow your design as T_masterlist diagram above with additional column for issues. i would also create a column that contain 'week-month-year' data to identify the particular tag is for which week.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

KingMooBotAuthor Commented:
The reason that I have 3 tables is because the each list's fields are not all identical.

ex. List1 could have: a,b,c,d
List2: a, c, d, f, g
List3: a, g

From this example the only common fields from all lists is 'a'
'c' is common between List1 and List 2 only.
'g' is common between List2 and List 3 only.

I don't think I should keep all data in one table if each list only utilizes a 1/3rd of the fields.

The main purpose of my program will be to view issues between common data, highlight changes to data upon subsequent data imports, and allow the user to designate which list has the correct data (ex. if List1_a = 'red' and List2_a = 'blue', if the user decides List1_a is correct then no o matter what data changes occur in the future on List2_a, for that specific tag, List1_a will always be designated as the governing data, unless List1_a no longer 'red' since 'red' was the value that the user originally approved to govern those fields)
KingMooBotAuthor Commented:

your comment for an "additional column for issues" on the T_MasterList sounds good. Would this be one field for each of the fields that are common between the 3 Lists?

List1: a=apple, b=banana, c=carrot
List2: a=apple, b=blueberry
List3: a=apple, b=banana, c=candy

T_MasterList.a_issues = no
T_MasterList.b_issues = yes
T_MasterList.c_issues = yes

Or were you suggesting that I pinpoint the issues?
hi king,
- either one also can. you can have the issue column as boolean then add another column called remarks to stated the issues description.

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now